Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 842
  • Last Modified:

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"

KeyWord = Forms("Suche").KatSuche.Text

If Forms("Suche").KatVor <> "" Then
VorD = Forms("Suche").KatVor.Value
End If

If Forms("Suche").KatNach <> "" Then
NachD = Forms("Suche").KatNach.Value
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 & "'"
1 Solution
VorD = "#22.02.2222#"
NachD = "#11.11.1972#"
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#"
fizherAuthor Commented:
I did try both: using different date format (i.e. 02/22/2222) and using # with both date formats - didn't help
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Put these in your SQL

Format(VorD, "mm/dd/yyyy")
Format(NachD, "mm/dd/yyyy")
Do VorD and NachD have to be dynamic, or are they fixed dates ?
fizherAuthor Commented:
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
Does this work (using DAO) ?

Set rst = CurrentDb.OpenRecordset("SELECT ProductID FROM  QMain WHERE [Kategorie] like '*" & KeyWord & "*' And [KatDatum] > #" & Format(NachD,"mm/dd/yyyy") & "# And [KatDatum] < " & Format(VorD,"mm/dd/yyyy"))
If rst.EOF Then
   'No products found
   'Products found
End If

Also, is QMain a query or a table ? Does the DCount line work if you remove the criteria ?
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
fizherAuthor Commented:
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.

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now