Comparing dates in Access VBA  - Syntax Errors / Type Mismatch

Posted on 2003-03-14
Medium Priority
Last Modified: 2012-06-27
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 & "'"
Question by:fizher
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8135429
VorD = "#22.02.2222#"
NachD = "#11.11.1972#"
LVL 41

Expert Comment

ID: 8135507
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#"

Author Comment

ID: 8135977
I did try both: using different date format (i.e. 02/22/2222) and using # with both date formats - didn't help
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Expert Comment

ID: 8136015
Put these in your SQL

Format(VorD, "mm/dd/yyyy")
Format(NachD, "mm/dd/yyyy")
LVL 41

Expert Comment

ID: 8136047
Do VorD and NachD have to be dynamic, or are they fixed dates ?

Author Comment

ID: 8136772
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
LVL 41

Accepted Solution

shanesuebsahakarn earned 1500 total points
ID: 8136981
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 ?
LVL 28

Expert Comment

ID: 8138206
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

Author Comment

ID: 8150971
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

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question