Too Few Parameters 2 (1 Viewer)

jamhs · Jul 11, 2017

Hi All

I'm abit struck on open this recordset and with Parameters with a Query

Its ran through a Button on a form

Code:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("TeamADC")

rs.Edit
rs("Approved") = True
rs("TMS") = Me.MTS
rs.Update

Code:

Query:

SELECT Log.Name, Log.DateT, Log.Comments, Log.Approved, Log.Declined, Log.Cancelled
FROM Log
WHERE (((Log.EmpName)=[Forms]![MyTeam]![EmpName]) AND ((Log.DateReq)=[Forms]![MyTeam]![DateT]));

I end up with Too Few Parameters 2 can anyone help.

isladogs · Jul 11, 2017

You need delimiters for each item in the WHERE clause

Code:

SELECT Log.Name, Log.DateT, Log.Comments, Log.Approved, Log.Declined, Log.Cancelled
FROM Log
WHERE (((Log.EmpName)= '" & [Forms]![MyTeam]![EmpName] & "') AND ((Log.DateReq)= #" & [Forms]![MyTeam]![DateT] & "#));

You should have " at the start & end as well

The line above looks odd to me

Code:

rs.UpdateQuery:

I've never done it like this anyway

Galaxiom · Jul 11, 2017

ridders said:
You need delimiters for each item in the WHERE clause

Code:

SELECT Log.Name, Log.DateT, Log.Comments, Log.Approved, Log.Declined, Log.Cancelled FROM Log WHERE (((Log.EmpName)= '" & [Forms]![MyTeam]![EmpName] & "') AND ((Log.DateReq)= #" & [Forms]![MyTeam]![DateT] & "#));

You should have " at the start & end as well

To explain a little further. Opening a Recordset is a method of CurrentDb. The db engine knows nothing of the Access objects so you have to concatenate the values and delimiters as ridders shows. Either that or append the parameters to the querydef first.

You also need to format the dates to mm/dd/yyyy if you are not in a US date region.

Code:
The line above looks odd to me

Code:

rs.UpdateQuery:

I've never done it like this anyway

I think the post lost the new line after Update and the OP was just showing the SQL for TeamADC query.

jamhs · Jul 11, 2017

Thank You Ridders

The rs.updatequery was meant to be seperate it's just rs.update.

I have looked around and this is how l have found to do it but if anyone knows a better way please tell me.

Orthodox Dave · Jul 11, 2017

Galaxiom is sadly right about the date format following the US format by default. It is also inconsistent as I found when I just tested in the immediate window:

Code:

?month(#02/03/17#)

gives 2 as the answer but

Code:

?month(#22/03/17#)

gives 3 as the answer.

So it assumes the first segment is the month unless that segment is >12 in which case it assumes the second segment is the month. Access should clear up the inconsistency. If the second example above caused a runtime error, at least we'd know what was going on.

This is pretty grim if your application relies on comparing dates (how many days between them, or what is the date 16 days later etc). My regional settings are set for UK, but that doesn't stop the above inconsistency.

Minty · Jul 11, 2017

@Dave - This is a known issue - have a read here for a full description and some sensible ways to work around it http://allenbrowne.com/ser-36.html

I personally use a function to force all dates in SQL statements to the correct format.

Galaxiom · Jul 11, 2017

Orthodox Dave said:
Galaxiom is sadly right about the date format following the US format by default. It is also inconsistent as I found when I just tested in the immediate window:

Code:

?month(#02/03/17#)

gives 2 as the answer but

Code:

?month(#22/03/17#)

gives 3 as the answer.

So it assumes the first segment is the month unless that segment is >12 in which case it assumes the second segment is the month. Access should clear up the inconsistency. If the second example above caused a runtime error, at least we'd know what was going on.

Try these to really do your head in:

Code:

? Day(#31/4/17#)

? Year(#29/2/17#)

It isn't Access or even VBA at fault. It is the Windows API itself which tries to make a valid date any way it can.

isladogs · Jul 11, 2017

Well of course Year(#29/2/17#) is 2029 - what else could it be?!?
Similarly Year(#31/11/17#) is 1931 - why not 2031?

And on a different note, try this in the Immediate window
?DateDiff("d",#9/2/1752#,#9/14/1752#)

The answer you get is 12, but it SHOULD be 1 (at least in the UK)
No I haven't lost my marbles ...

1752 began on 1 January. To align the calendar in use in England to that on the continent, the Gregorian calendar was adopted: and the calendar was advanced by 11 days: Wednesday 2 September 1752 was followed by Thursday 14 September 1752. The year 1752 was thus a short year (355 days) as well.

This caused riots in parts of Britain as some people were aggrieved they had lost 11 days of their lives!
Another form of Brexit perhaps....

Orthodox Dave · Jul 11, 2017

Thanks Minty,

That's an excellent article by Allen Browne. My examples were VBA not SQL so if in doubt, calculate using the US way then convert back.

I now need to revisit all my date coding because I had wrongly thought Access now used regional settings in all cases. It's a good lesson to learn. No good waiting for Microsoft - Allen's article was 9 years ago and MS have done absolutely nothing to help.

Orthodox Dave · Jul 11, 2017

Colin - Don't get me started. Please.

Orthodox Dave · Jul 11, 2017

OK - in my opinion the best date format is 2017-07-21, because if you list these in a folder, they go into date order.

Minty · Jul 11, 2017

That format is by far the best to use. yyyy/mm/dd .
SQL Server uses that as well for obvious reasons

isladogs · Jul 11, 2017

Colin - Don't get me started. Please.

Now would I?

Orthodox Dave · Jul 11, 2017

yyyy/mm/dd has as little chance of being adopted as starting numbers with 0. The new millennium was actually on 1st Jan 2001, because the years started at 1AD (no zero). Or perhaps it was 12th Jan 2001 (to allow for the 11 lost days, Colin). Oh you've got me started now. Stop me before I get onto "3 times more" = "3 times as much"!

isladogs · Jul 11, 2017

Sorry jamhs - we've hijacked your thread!
The answer is in here somewhere ....

Dave - you're a bit like my lab with a bone - you can't leave it alone

Orthodox Dave · Jul 11, 2017

Sorry I'll shut up now.

Galaxiom · Jul 12, 2017

ridders said:
Well of course Year(#29/2/17#) is 2029 - what else could it be?!?

The point is that it makes date entry errors intractable. Windows should stick to the regional settings and error on anything else.

Similarly Year(#31/11/17#) is 1931 - why not 2031?

It is a user setting.
Control Panel > Region and Language > Date Tab > Calendar

BTW There is also a setting right underneath it for the first day of the week. For reasons only known to Microsoft, Australia Region defaults to Monday. Must be confusing for the Seventh Day Adventists.

It is a gotcha for Aussies because it misaligns the Weekday() and WeekDayName() functions. WeekDayName() honours the setting but WeekDay() doesn't.

Do any other regions get the wrong answer for this expression with default settings?

Code:

 ? WeekdayName(Weekday(Date))

isladogs · Jul 12, 2017

Originally Posted by ridders
Well of course Year(#29/2/17#) is 2029 - what else could it be?!?
The point is that it makes date entry errors intractable. Windows should stick to the regional settings and error on anything else.

Sorry, I was being sarcastic (again ...) - bad habit

Quote:
Similarly Year(#31/11/17#) is 1931 - why not 2031?
It is a user setting.
Control Panel > Region and Language > Date Tab > Calendar

I can't see any reason why this explains the example I gave
Why is your first example 2029 when mine is 1931?
I have UK date settings dd/mm/yyyy

As you say, if first day of the week is set to Monday then

Code:

? WeekdayName(Weekday(Date))

gives the wrong day
e.g. today is Wed but it gives Thur
Obviously 'fixed' by adding -1, but unhelpful

Orthodox Dave · Jul 12, 2017

The point is that it makes date entry errors intractable. Windows should stick to the regional settings and error on anything else.

Despite all my previous bluster, thinking it through now, how could Microsoft do this? This date problem only arises when Access has to interpret a string as a date (e.g. it must decide what is meant by "#26/04/17#"?) If Access interpreted this string according to regional settings, it would mean different dates in different regions, or produce a runtime error. So this isn't the solution.

What Microsoft should do is stick to one rule for date format in strings and make any other format an error. And make this very clear. Currently it's the worst of all worlds, producing absurdities as quoted above by Ridders.

jamhs · Jul 12, 2017

Thanks All l have Managed to put this together:

PHP:

strSQL = "SELECT Log.EmpName, Log.DateReq, Log.MGR_Comments, Log.Approved, Log.Declined, Log.Cancelled FROM Log"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
rs.MoveLast
rs.Filter = [DateReq] = [Forms]![MyTeam]![DateReq] And [EmpName] = [Forms]![MyTeam]![EmpName]
Set rsFiltered = rs.OpenRecordset
Debug.Print rsFiltered.RecordCount
rsFiltered.Edit
rsFiltered!Approved = True
rsFiltered.Update

PHP:

The Debug record count comes back with one and the right record but is not actioning what l want.

Can anyone tell me what l have done wrong here

Thank you

Too Few Parameters 2 (1 Viewer)

New member

MVP / VIP

Super Moderator

New member

Home Developer

AWF VIP

Super Moderator

MVP / VIP

Home Developer

Home Developer

Home Developer

AWF VIP

MVP / VIP

Home Developer

MVP / VIP

Home Developer

Super Moderator

MVP / VIP

Home Developer

New member

Similar threads

Users who are viewing this thread