Using SQL with Recordset

[reginaldmerritt]

Ok so while we are on a roll here perhaps you could point me in the right direction on the next stage.

I'm trying to use the SQL statement to open a recordset so i can grap values from the record with a date closest today.

The code is as follows:

Set ChargeRatesRecordSet = CurrentDb.OpenRecordset("Select TOP 1 VolunteerDisclosureTotal, StandardDisclosureTotal, EnchancedDisclosureTotal, POVAPOCADisclosureTotal, EnhancedPOVAPOCADisclosureTotal, ISARegOnlyTotal, EnhancedISARegTOTAL, DateAsOf FROM ChargeRates WHERE [DateAsOf] <= Date ORDER BY [DateAsOf] DESC;")

I get runtime error 3061 too few parameters ????

[NeoPa]

This thread was split off from Open form where date is closest to today as it's a new and separate question.

[NeoPa]

As this code is mostly not visible Reg, I had to copy it to somewhere else to view it. When I did I looked but found nothing amiss. That may be because it's laid out so haphazardly, but I doubt it (I looked quite carefully). The only thing I can think of is that one of your items is mis-spelled. There's nothing obviously wrong with the SQL, the format of which seems fine.

[reginaldmerritt]

Thanks

It's a problem with the where statment i think

The code below works.

Set ChargeRatesRecordSet = CurrentDb.OpenRecordset("
Select Top 1 DateAsOf
FROM ChargeRates 
ORDER BY [DateAsOf] DESC;")

But this code does not.

Set ChargeRatesRecordSet = CurrentDb.OpenRecordset("
Select Top 1 DateAsOf
FROM ChargeRates 
WHERE [DateAsOf] <= Date
ORDER BY [DateAsOf] DESC;")

[reginaldmerritt]

Your right NeoPa the above wan't working becasue i missed of the brackets after 'Date' so the where statment should have been 'Where [DateAsOf] <= Date()'

Really i want the Where statement in the SQL used to Filter the Recordset to use a Field on the Form, but I think i have found a solution.

So the code now looks like this

Set ChargeRatesRecordSet = CurrentDb.OpenRecordset("
Select Top 1 DateAsOf
FROM ChargeRates 
WHERE [DateAsOf] <= #" & Me.DateCRBSent & "#
ORDER BY [DateAsOf] DESC;")

Needs testing to make sure it actaually works out but no error messages so far.

[NeoPa]

Nice work. I couldn't ask for the problem to be illustrated more clearly :)

Unfortunately the SQL looks ok to me so I'm still somewhat at a loss.

Just try this to see if it has any effect :

WHERE ([DateAsOf] <= Date())

Otherwise that is really bizarre. We may have to look at a cut-down version of your database (if that doesn't work).

[NeoPa]

Your right NeoPa the above wan't working becasue i missed of the brackets after 'Date' so the where statment should have been 'Where [DateAsOf] <= Date()'

I should have read this first really :D

Really i want the Where statement in the SQL used to Filter the Recordset to use a Field on the Form, but I think i have found a solution.

...
WHERE [DateAsOf] <= #" & Me.DateCRBSent & "#
...

Fundamentally correct, but for portable and reliable use not quite.

If you can get the form without the date filled in the Nulls need to be handled.

The format of a date literal in SQL doesn't always match the computer's default display (though it often does in the USA). It's certainly not reliable to rely on that. See Literal DateTimes and Their Delimiters (#) for more on this.

[reginaldmerritt]

Great shout Neopa i'll make sure i put this to good use.

I make sure the value of the field with the date is check for null state first and then formated to m/d/y to be safe before being used in the SQL statement.

[NeoPa]

Excellent. Not everybody (indeed very few) gets the importance of that the first time of telling.

You could consider using Nz() for handling Null values.

[reginaldmerritt]

Usually that would be a good option, however, the sub routine needs to inform the user is the varaible is null so they can go back to a previous form to fix the error.

If there was a standard value to fill any varaibles found to be null then Nz() is defintly the function to use.

Thanks.

[NeoPa]

True. True.

If the controls on a previous form shouldn't be Null then possibly change the form to ensure Null values are not accepted.

[reginaldmerritt]

Yes indeed, that is where the checks should be made. However, i've still put in a check for an error value becuase a user never uses the program or reads all the error messages as you expect them to.

[NeoPa]

Belt & Braces. Always wise in coding :)