Querying recordsets with user input as dates

[pengiliverpool]

i have created a recordset from a table and then using user input as start date and end date i take a record count of the record set. the code works ok but the count it returns is not correct is certain instances.
i have copied the code into a query and the results there are fine but they are from the actual table not a recordset of the table. does anyone have any experience of passing date variables into recordsets?
my code is as follows:
Private Sub Form_Load()
Dim Db As DAO.Database
Dim rstable As DAO.Recordset
Dim Date1 As Date
Dim Date2 As Date
Date1 = Form_Menu.Combo1.Value
Date2 = Form_Menu.Combo2.Value
Dim sResults As String
Set Db = CurrentDb
Set rstable = Db.OpenRecordset("Saved_Data", dbOpenDynaset)
sResults = "SELECT * FROM Saved_Data WHERE (((Saved_Data.A5)> #" & Date1 & "# And (Saved_Data.A5) < #" & Date2 & "# ));"
Set rstable = Db.OpenRecordset(sResults, dbOpenDynaset)
If rstable.EOF = False Then
rstable.MoveLast
num1 = rstable.RecordCount
Else
num1 = "0"
End If
Text1.Value = num1

when i ask for januarys records so start date 1st jan and end date 31st it gives correct number. when i change to 31st dec and 1 feb which should give same result it gives the figure as 0

if anyone could help that would be brilliant i have been working on this for days now

 

[namliam]

Make sure your date1 and 2 are the dates you expect it to be and make sure that your A5 is a datefield as well.

It is probably that one or both are wrong.

PS Welcome to AWF

 

[Brianwarnock]

when i ask for januarys records so start date 1st jan and end date 31st it gives correct number. when i change to 31st dec and 1 feb which should give same result it gives the figure as 0

if anyone could help that would be brilliant i have been working on this for days now

Why should it give the same result when 2 extra days are included? Did you remember to change the year for 31st Dec?

Brian

 

[pengiliverpool]

Hi,
both date fields are passed from a calendar control.
when i use the calendar control in a query the result is perfect every time.
A5 is set as datefield.
it seems to be finding everything before the end date but ignoring the start date.
i.e. when i search for jan i get jans result when i search for feb i get jan + feb as the result.
if this was the only issue i could work around it and just work from one date but when i search with 01/01/2009 as start date and 31/01/2009 as end date i get jans results then when i use 31/12/2008 and 01/02/2009 you would think i would get the same result but it returns 0.
does the recordset take the same format as the table? i.e. date fields are carried as date fields?

 

[namliam]

This has to have something to do with the fact that the dates are interperted as strings...

IT JUST (almost) HAS TO

The only way there is no data between your dates is if the dates are actually strings where nothing >"31" and <"01", as this is just impossible

another possibility MAY be that the 01/02/2009 is your euro format and is actually being interperted as us format (mm/dd), i.e 02-jan-2009... There wont be much if anything between 31-Dec and 02-Jan.
Try adding a format to both your dates:
...#" & Format(date1, "MM/DD/YYYY") & "# And (Saved_Data.A5) < #" & Format(date2, "MM/DD/YYYY") & "#....
To see if that is the case, Jet SQL REQUIRES the US Format

 

[pengiliverpool]

thank you namlian it was the date format.
i have been looking at it for ages and couldnt understand why it was happening, cheers.

 

[pengiliverpool]

i have now tried to put a futher parameter in to the SQl statement and am getting a run time error 3061 to few parameters expected.
here is my code that works
sResults = "SELECT * FROM Saved_Data WHERE (((Saved_Data.A5)>= #" & Format(Date1, "MM/DD/YYYY") & "# And (Saved_Data.A5) <= #" & Format(Date2, "MM/DD/YYYY") & "# ));"
Set rstable = Db.OpenRecordset(sResults, dbOpenDynaset)

and below is my code with my additional with the extra parameter in that is giving me the error:

sResults = "SELECT * FROM Saved_Data WHERE (((Saved_Data.A4)=[Forms]![Menu]![Combo156]) AND ((Saved_Data.A5)>= #" & Format(Date1, "MM/DD/YYYY") & "# And (Saved_Data.A5) <= #" & Format(Date2, "MM/DD/YYYY") & "# ));"
Set rstable = Db.OpenRecordset(sResults, dbOpenDynaset)

Can anyone help?

 

[namliam]

You dont have a additional parameter in there you have more text in there...

a parameter like the Date1 and Date2 needs to be substituted in exactly like your doing with the date1 and date2 no matter where that parameter resides.
Also take note that like a date is 'captured' by # on either side, a text has to be 'captured' by ' on either side, while a number doesnt need anything

Lastly, when posting code, or using SQL in code... please keep it
1) Readable, both for yourself an dothers
2) Use [ code ] and [ /code ] tags around your code to preserve its readablity.
That is without the spaces offcourse or press the # button on the menu bar of the post.

I.e. your reformated (but still broken) code:

sResults = ""
sResults = sResults & " SELECT * "
sResults = sResults & " FROM Saved_Data "
sResults = sResults & " WHERE (((Saved_Data.A4) = [Forms]![Menu]![Combo156]) "
sResults = sResults & "   AND  ((Saved_Data.A5) >= #" & Format(Date1, "MM/DD/YYYY") & "# "
sResults = sResults & "   AND   (Saved_Data.A5) <= #" & Format(Date2, "MM/DD/YYYY") & "# ));"

 

[pengiliverpool]

You dont have a additional parameter in there you have more text in there...

a parameter like the Date1 and Date2 needs to be substituted in exactly like your doing with the date1 and date2 no matter where that parameter resides.
Also take note that like a date is 'captured' by # on either side, a text has to be 'captured' by ' on either side, while a number doesnt need anything

Lastly, when posting code, or using SQL in code... please keep it
1) Readable, both for yourself an dothers
2) Use [ code ] and [ /code ] tags around your code to preserve its readablity.
That is without the spaces offcourse or press the # button on the menu bar of the post.

I.e. your reformated (but still broken) code:

sResults = ""
sResults = sResults & " SELECT * "
sResults = sResults & " FROM Saved_Data "
sResults = sResults & " WHERE (((Saved_Data.A4) = [Forms]![Menu]![Combo156]) "
sResults = sResults & "   AND  ((Saved_Data.A5) >= #" & Format(Date1, "MM/DD/YYYY") & "# "
sResults = sResults & "   AND   (Saved_Data.A5) <= #" & Format(Date2, "MM/DD/YYYY") & "# ));"

Hi Namilian,
Thanks for getting back in touch, you were a great help last time. I have changed the set up slightly and declared the parameter as a string so it now looks like:

Dim Date1 As Date
Dim Date2 As Date
Dim Dept As String
Date1 = Form_Menu.Combo1.Value
Date2 = Form_Menu.Combo2.Value
Dept = Form_Menu.Combo156.Value
 
sResults = ""
sResults = sResults & " SELECT * "
sResults = sResults & " FROM Saved_Data "
sResults = sResults & " WHERE (((Saved_Data.A4) = Dept"
sResults = sResults & " AND ((Saved_Data.A5) >= #" & Format(Date1, "MM/DD/YYYY") & "# "
sResults = sResults & " AND (Saved_Data.A5) <= #" & Format(Date2, "MM/DD/YYYY") & "# ));"

this now gives me a syntax error, i think it may be as you described using a # or ' to capture the data type.

 

[namliam]

You are not substituting in this variable.... why not??
sResults = sResults & " WHERE (((Saved_Data.A4) = Dept"
It should look like so:
sResults = sResults & " WHERE (((Saved_Data.A4) = '" & Dept &"'"
That is assuming it is a text your looking for, if its a number remove the two '

 

[pengiliverpool]

Hi namilian,
this has now caught the third parameter and is recognising it. when i hover over it in debug it shows the input selected on the form.
I am now getting a new error run time 3075 error in syntax. in debug it highlights the last

 

[pengiliverpool]

Hi namilian,
this has now caught the third parameter and is recognising it. when i hover over it in debug it shows the input selected on the form.
I am now getting a new error run time 3075 error in syntax. in debug it highlights the last line of my code as posted below:

sResults = ""
sResults = sResults & " SELECT * "
sResults = sResults & " FROM Saved_Data "
sResults = sResults & " WHERE (((Saved_Data.A4) = '" & Dept & "'"
sResults = sResults & "   AND  ((Saved_Data.A5) >= #" & Format(Date1, "MM/DD/YYYY") & "# "
sResults = sResults & "   AND   (Saved_Data.A5) <= #" & Format(Date2, "MM/DD/YYYY") & "# ));"
Set rstable = Db.OpenRecordset(sResults, dbOpenDynaset)

once again thanks for your help although i am making slow progress it is definite progess and this should be the last the issue to resolve.

kind regards
dave

 

[namliam]

Have you tried looking up the Openrecordset command in the access help

I will assume you have properly declared the Db varaible and filled it accoordingly?

Try setting a debug before the Set rstable
Debug.print sResults

Then after running the code you can press ALT+F11 and you will see the full sql, you can copy paste this into a 'real' query then see if that runs or not.