handling date ranges in Dynamic QBF

  • Thread starter Thread starter MaryAnn
  • Start date Start date
M

MaryAnn

Thanks for reading. Hope you can help.

How do I handle date ranges (code) in a dynamic query?

Here's what I have so far... Date of Hire needs to be a
range rather than a static value. In the table the date
was inputed as MM/DD/YY. Thanks for any assistance.



Option Compare Database
Option Explicit

Private Sub cmdRunQuery_Click()

Dim MyDatabase As Database
Dim MyQueryDef As QueryDef
Dim rs As Recordset
Dim where As Variant

Set MyDatabase = CurrentDb()

If ObjectExists("Queries", "qryDynamic_QBF") = True
Then
MyDatabase.QueryDefs.Delete "qryDynamic_QBF"
MyDatabase.QueryDefs.Refresh

End If

where = Null
where = where & " AND [Clock Number]= '" + Me!
[txtClock] + "'"
where = where & " AND [Last Name]= '" + Me!
[Text15] + "'"
where = where & " AND [Date of Hire]= '" + Me!
[Text4] + "'"
where = where & " AND [Location]= '" + Me![Text12]
+ "'"
where = where & " AND [Title]= '" + Me![Combo23]
+ "'"
where = where & " AND [Union]= '" + Me![Combo27]
+ "'"

Set MyQueryDef = MyDatabase.CreateQueryDef
("qryDynamic_QBF", _
"Select * from EADDataForPhotos " & (" where " + Mid
(where, 6) & ";"))

Set Recordset = MyDatabase.OpenRecordset("Select *
from EADDataForPhotos " & (" where " + Mid(where, 6)
& ";"))

If Recordset.RecordCount = 0 Then
MsgBox "No Records were found"
Else
DoCmd.OpenQuery "qryDynamic_QBF"
DoCmd.OpenForm "Search Results"
Forms![Search Results].Requery
DoCmd.Close acQuery, "qryDynamic_QBF"


End If

End Sub
 
MaryAnn said:
How do I handle date ranges (code) in a dynamic query?

Here's what I have so far... Date of Hire needs to be a
range rather than a static value. In the table the date
was inputed as MM/DD/YY. Thanks for any assistance.
[]
where = where & " AND [Date of Hire]= '" + Me!
[Text4] + "'"
Click to expand...
[]


You can use the Between operator for a range.

Note that it doesn't matter what the format of the field is
in the table. When you do this kind of thing, it's your
locale settings that can trip you up. To avoid these
issues, explicitly format the date to US format:

.... & " AND [Date of Hire] Between " & Format(Me!txtStart,
"\#m\/d\/yyyy\#") & " And " & Format(Me!txtEnd,
"\#m\/d\/yyyy\#")
 
-----Original Message-----
MaryAnn said:
How do I handle date ranges (code) in a dynamic query?

Here's what I have so far... Date of Hire needs to be a
range rather than a static value. In the table the date
was inputed as MM/DD/YY. Thanks for any assistance.
[]
where = where & " AND [Date of Hire]= '" + Me!
[Text4] + "'"
Click to expand...
[]


You can use the Between operator for a range.

Note that it doesn't matter what the format of the field is
in the table. When you do this kind of thing, it's your
locale settings that can trip you up. To avoid these
issues, explicitly format the date to US format:

.... & " AND [Date of Hire] Between " & Format(Me! txtStart,
"\#m\/d\/yyyy\#") & " And " & Format(Me!txtEnd,
"\#m\/d\/yyyy\#")
Click to expand...
 
Thanks Marsh! I really appreciate your help! MAB

-----Original Message-----
MaryAnn said:
How do I handle date ranges (code) in a dynamic query?

Here's what I have so far... Date of Hire needs to be a
range rather than a static value. In the table the date
was inputed as MM/DD/YY. Thanks for any assistance.
[]
where = where & " AND [Date of Hire]= '" + Me!
[Text4] + "'"
Click to expand...
[]


You can use the Between operator for a range.

Note that it doesn't matter what the format of the field is
in the table. When you do this kind of thing, it's your
locale settings that can trip you up. To avoid these
issues, explicitly format the date to US format:

.... & " AND [Date of Hire] Between " & Format(Me! txtStart,
"\#m\/d\/yyyy\#") & " And " & Format(Me!txtEnd,
"\#m\/d\/yyyy\#")
Click to expand...
 
Marsh,

Now it is telling me there is a syntax error and it is
highlighting the following:


Set MyQueryDef = MyDatabase.CreateQueryDef
("qryDynamic_QBF", _
"Select * from EADDataForPhotos " & (" where " + Mid
(where, 6) & ";"))

What might I be doing wrong?


-----Original Message-----
MaryAnn said:
How do I handle date ranges (code) in a dynamic query?

Here's what I have so far... Date of Hire needs to be a
range rather than a static value. In the table the date
was inputed as MM/DD/YY. Thanks for any assistance.
[]
where = where & " AND [Date of Hire]= '" + Me!
[Text4] + "'"
Click to expand...
[]


You can use the Between operator for a range.

Note that it doesn't matter what the format of the field is
in the table. When you do this kind of thing, it's your
locale settings that can trip you up. To avoid these
issues, explicitly format the date to US format:

.... & " AND [Date of Hire] Between " & Format(Me! txtStart,
"\#m\/d\/yyyy\#") & " And " & Format(Me!txtEnd,
"\#m\/d\/yyyy\#")
Click to expand...
 
Marsh,

Now it is telling me there is a syntax error and it is
highlighting the following:

Set MyQueryDef = MyDatabase.CreateQueryDef
("qryDynamic_QBF", _
"Select * from EADDataForPhotos " & (" where " + Mid
(where, 6) & ";"))
Click to expand...

The problem is most likely in the string where. Try adding
a MsgBox where line before the above so you can see if the
string really is what you intended.
--
Marsh
MVP [MS Access]


-----Original Message-----
MaryAnn said:
How do I handle date ranges (code) in a dynamic query?

Here's what I have so far... Date of Hire needs to be a
range rather than a static value. In the table the date
was inputed as MM/DD/YY. Thanks for any assistance.
[]
where = where & " AND [Date of Hire]= '" + Me!
[Text4] + "'"
Click to expand...
[]


You can use the Between operator for a range.

Note that it doesn't matter what the format of the field is
in the table. When you do this kind of thing, it's your
locale settings that can trip you up. To avoid these
issues, explicitly format the date to US format:

.... & " AND [Date of Hire] Between " & Format(Me! txtStart,
"\#m\/d\/yyyy\#") & " And " & Format(Me!txtEnd,
"\#m\/d\/yyyy\#")
Click to expand...
Click to expand...
 
Marsh,

I got that problem solved. Here's a new one - I put in
the date ranges and it comes up with my msg box which
says "no records found". BUT, when I go to the query, the
correct information is there - only it does not show it.
If I click the "show" box on the query design page, then
it shows the correct information. How do I get it
to "show"?

Let me know if you want me to send the query.

Thank you again Marsh - I really appreciate your help.

Mary Ann

-----Original Message-----
Marsh,

Now it is telling me there is a syntax error and it is
highlighting the following:

Set MyQueryDef = MyDatabase.CreateQueryDef
("qryDynamic_QBF", _
"Select * from EADDataForPhotos " & (" where " + Mid
(where, 6) & ";"))
Click to expand...

The problem is most likely in the string where. Try adding
a MsgBox where line before the above so you can see if the
string really is what you intended.
--
Marsh
MVP [MS Access]


-----Original Message-----
MaryAnn wrote:
How do I handle date ranges (code) in a dynamic query?

Here's what I have so far... Date of Hire needs to be a
range rather than a static value. In the table the date
was inputed as MM/DD/YY. Thanks for any assistance.

[]
where = where & " AND [Date of Hire]= '" + Me!
[Text4] + "'"
[]


You can use the Between operator for a range.

Note that it doesn't matter what the format of the
Click to expand...
field
is
in the table. When you do this kind of thing, it's your
locale settings that can trip you up. To avoid these
issues, explicitly format the date to US format:

.... & " AND [Date of Hire] Between " & Format(Me! txtStart,
"\#m\/d\/yyyy\#") & " And " & Format(Me!txtEnd,
"\#m\/d\/yyyy\#")
Click to expand...
Click to expand...
.
Click to expand...
 
Marsh,

I got that problem solved. Here's a new one - I put in
the date ranges and it comes up with my msg box which
says "no records found". BUT, when I go to the query, the
correct information is there - only it does not show it.
If I click the "show" box on the query design page, then
it shows the correct information. How do I get it
to "show"?

Let me know if you want me to send the query.
Click to expand...

I am lost here so, Yes, please post the query along with an
explanation of the information you are not getting.
--
Marsh
MVP [MS Access]


-----Original Message-----
Click to expand...
Marshall said:
The problem is most likely in the string where. Try adding
a MsgBox where line before the above so you can see if the
string really is what you intended.
-----Original Message-----
MaryAnn wrote:
How do I handle date ranges (code) in a dynamic query?

Here's what I have so far... Date of Hire needs to be a
range rather than a static value. In the table the date
was inputed as MM/DD/YY. Thanks for any assistance.

[]
where = where & " AND [Date of Hire]= '" +
Me![Text4] + "'"
[]


Marshall Barton wrote:
You can use the Between operator for a range.

Note that it doesn't matter what the format of the field
is
in the table. When you do this kind of thing, it's your
locale settings that can trip you up. To avoid these
issues, explicitly format the date to US format:

.... & " AND [Date of Hire] Between " & Format(Me!
txtStart,
"\#m\/d\/yyyy\#") & " And " & Format(Me!txtEnd,
"\#m\/d\/yyyy\#")
Click to expand...
Click to expand...
Click to expand...
 
Back
Top