fizher
asked on
Comparing dates in Access VBA - Syntax Errors / Type Mismatch
Hi there,
I'm having problems in Access VBA to compare a date in a table with dates from a search form. I want to limit the table output to products that have been inserted to the table in a certain category in a certain period of time. For this I pull Keyword for the category and VorD and NachD as limiting dates.
For some reason I always get a syntax error or field type mismatch in the IfDCount line (and the one below). I also trying direct SQL phrasing - didn't help. Variables and Table fields both defined as short date. It's not the date format (03/15/2002), cause it's the European version. I also tried surrounding the dates with # instead of ' (or leaving those bits totally away)... Please help - Code is below - Thanks a lot, Chris
-----------------
Function KatSuche()
Dim KeyWord As String
Dim VorD As Date
Dim NachD As Date
KeyWord = ""
VorD = "22.02.2222"
NachD = "11.11.1972"
Forms("Suche").KatSuche.Se
KeyWord = Forms("Suche").KatSuche.Te
If Forms("Suche").KatVor <> "" Then
Forms("Suche").KatVor.SetF
VorD = Forms("Suche").KatVor.Valu
End If
If Forms("Suche").KatNach <> "" Then
Forms("Suche").KatNach.Set
NachD = Forms("Suche").KatNach.Val
End If
If DCount("ProduktID", "QMain", "[Kategorie] like '*" & KeyWord & "*' And [KatDatum] > " & NachD & " And [KatDatum] < " & VorD) > 0 Then
DoCmd.OpenForm "Produkte Eingabe", acFormDS, , "[Kategorie] like '*" & KeyWord & "*' And [KatDatum] > '" & NachD & "' And [KatDatum] < '" & VorD & "'"
DoCmd.Maximize
Else
...
I'm having problems in Access VBA to compare a date in a table with dates from a search form. I want to limit the table output to products that have been inserted to the table in a certain category in a certain period of time. For this I pull Keyword for the category and VorD and NachD as limiting dates.
For some reason I always get a syntax error or field type mismatch in the IfDCount line (and the one below). I also trying direct SQL phrasing - didn't help. Variables and Table fields both defined as short date. It's not the date format (03/15/2002), cause it's the European version. I also tried surrounding the dates with # instead of ' (or leaving those bits totally away)... Please help - Code is below - Thanks a lot, Chris
-----------------
Function KatSuche()
Dim KeyWord As String
Dim VorD As Date
Dim NachD As Date
KeyWord = ""
VorD = "22.02.2222"
NachD = "11.11.1972"
Forms("Suche").KatSuche.Se
KeyWord = Forms("Suche").KatSuche.Te
If Forms("Suche").KatVor <> "" Then
Forms("Suche").KatVor.SetF
VorD = Forms("Suche").KatVor.Valu
End If
If Forms("Suche").KatNach <> "" Then
Forms("Suche").KatNach.Set
NachD = Forms("Suche").KatNach.Val
End If
If DCount("ProduktID", "QMain", "[Kategorie] like '*" & KeyWord & "*' And [KatDatum] > " & NachD & " And [KatDatum] < " & VorD) > 0 Then
DoCmd.OpenForm "Produkte Eingabe", acFormDS, , "[Kategorie] like '*" & KeyWord & "*' And [KatDatum] > '" & NachD & "' And [KatDatum] < '" & VorD & "'"
DoCmd.Maximize
Else
...
I suspect that it is the date format. Jet normally expects dates to be in the US format when using it in code but I cannot remember if this applies to domain aggregate functions or not. Try:
VorD = "#02/22/2222#"
NachD = "#11/11/1972#"
VorD = "#02/22/2222#"
NachD = "#11/11/1972#"
ASKER
I did try both: using different date format (i.e. 02/22/2222) and using # with both date formats - didn't help
Put these in your SQL
Format(VorD, "mm/dd/yyyy")
Format(NachD, "mm/dd/yyyy")
Format(VorD, "mm/dd/yyyy")
Format(NachD, "mm/dd/yyyy")
Do VorD and NachD have to be dynamic, or are they fixed dates ?
ASKER
They are dynamic. I just have "backup" dates in the code in case that the search fields are emtpy.
PS: It doesn't work with the fixed nor with the dynamic values
PS: It doesn't work with the fixed nor with the dynamic values
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can we please clarify the situation with dates.
1. Dates are not stored in "different formats". The Date/Time field stores the date as the integer and the time as the fraction of number, today 14th March 2003 is number 37,694, noon is .5, 6am is .25 and 6pm is .75
2. You can use an inputmask to assist in the formating of the date
3. You can format a date to a specific format.
4. Input and output of dates us the regional setting of your PC / User unless you specufy otherwise.
5. Access front end is regionalised to use your local settings for dates etc.
6. and this is the important one to remember SQL IS NOT Internationalised, your dates has to be MM/DD/YY or MM/DD/YYYY and a date is always wrapped with #'s.
To demonstrated this point 6 create a new query and enter a criteria against a date field and type the date in your local setting, in my case #31/12/03#. Now change to the SQL view of the query and you will see the query will be something like SELECT tablename.* FROM tablename WHERE tablename.fieldname = #12/31/2003#
HTH Andrew
1. Dates are not stored in "different formats". The Date/Time field stores the date as the integer and the time as the fraction of number, today 14th March 2003 is number 37,694, noon is .5, 6am is .25 and 6pm is .75
2. You can use an inputmask to assist in the formating of the date
3. You can format a date to a specific format.
4. Input and output of dates us the regional setting of your PC / User unless you specufy otherwise.
5. Access front end is regionalised to use your local settings for dates etc.
6. and this is the important one to remember SQL IS NOT Internationalised, your dates has to be MM/DD/YY or MM/DD/YYYY and a date is always wrapped with #'s.
To demonstrated this point 6 create a new query and enter a criteria against a date field and type the date in your local setting, in my case #31/12/03#. Now change to the SQL view of the query and you will see the query will be something like SELECT tablename.* FROM tablename WHERE tablename.fieldname = #12/31/2003#
HTH Andrew
ASKER
Actually I in the end I worked with the follwing taken from Allen Brown's Access Tips (http://users.bigpond.net.au/abrowne1/)
Dates in Strings
If your program may be used outside of the United States (Britain, Korea, etc.), you need to be aware that many countries use other date formats such as dd/mm/yy, yy/mm/dd. Jet SQL requires dates in American format (mm/dd/yy). Dates entered as criteria in the Query Design grid are correctly interpreted by Access.
When you build SQL strings in VBA, you must explicitly format the dates for Jet. Failure to do so means you code is broken if the user changes the Regional Settings in Control Panel. When you format the date, the Format() function replaces the slashes in the format string with the date separator defined in Control Panel. The slashes in the format string must therefore be preceeded by backslashes to indicate you want literal slash characters.
Solution:
In a general module, create a function that returns a string representing the date as #mm/dd/yyyy#.
Function SQLDate(vDate As Variant) As String
If IsDate(vDate) Then
SQLDate = "#" & Format$(vDate, "mm\/dd\/yyyy") & "#"
End If
End Function
Always wrap dates passed to Jet in this function, e.g.:
Dim strWhere as String
If Not IsNull(Me.StartDate) Then
strWhere = "[InvoiceDate] >= " & SQLDate(Me.StartDate)
End If
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
Dates in Strings
If your program may be used outside of the United States (Britain, Korea, etc.), you need to be aware that many countries use other date formats such as dd/mm/yy, yy/mm/dd. Jet SQL requires dates in American format (mm/dd/yy). Dates entered as criteria in the Query Design grid are correctly interpreted by Access.
When you build SQL strings in VBA, you must explicitly format the dates for Jet. Failure to do so means you code is broken if the user changes the Regional Settings in Control Panel. When you format the date, the Format() function replaces the slashes in the format string with the date separator defined in Control Panel. The slashes in the format string must therefore be preceeded by backslashes to indicate you want literal slash characters.
Solution:
In a general module, create a function that returns a string representing the date as #mm/dd/yyyy#.
Function SQLDate(vDate As Variant) As String
If IsDate(vDate) Then
SQLDate = "#" & Format$(vDate, "mm\/dd\/yyyy") & "#"
End If
End Function
Always wrap dates passed to Jet in this function, e.g.:
Dim strWhere as String
If Not IsNull(Me.StartDate) Then
strWhere = "[InvoiceDate] >= " & SQLDate(Me.StartDate)
End If
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
VorD = "#22.02.2222#"
NachD = "#11.11.1972#"