fixing the sql error in vba access

Posted on 2015-01-22
Last Modified: 2016-02-10
I`m getting run time error 3252 in this line:
If DCount("*", "tblBranchsSpecialDayOff", "SQLDate([DayOff] = #" & SQLDate(dteStartDate) & "# AND [BranchNumber] =" & IntBranchNumber & "") = 1 Then    'NOT Holiday

Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne., June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

what i`m trying to do in vba access is more to fix the default sql date syntax as mm/dd/yyyy, to dd/mm/yyyy by using the function upbove .
Question by:drtopserv
LVL 18

Accepted Solution

Simon earned 125 total points
ID: 40564137
DCount expects an American date format (mm/dd/yyyy). For unambiguous dates e.g. 31st December it will accept dd/mm/yy (and convert it on the fly), but for ambiguous dates e.g. 7th May it will only give a correct result if the date is provided as #5/7/2014#.

Compare these examples from a table, where the number of mailings can only INCREASE over time:

I think your problem is that you're using the SQLDate() function on the field name as well as the value. It should be:

If DCount("*", "tblBranchsSpecialDayOff", "[DayOff] = #" & SQLDate(dteStartDate) & "# AND [BranchNumber] =" & IntBranchNumber & "") = 1 Then    'NOT Holiday

Open in new window

LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 125 total points
ID: 40564287
I may be interpreting this incorrectly but...

To me, you should just use the raw date for your expression...

You "conversion" is not needed, ...if all you need is a dcount here.
You should only need the conversion if you are retuning the SQLdate..

So try this, and see if the correct "value" is returned:
If DCount("*", "tblBranchsSpecialDayOff", "[DayOff] = #" & (dteStartDate) & "# AND [BranchNumber] =" & IntBranchNumber & "") = 1 Then

LVL 35

Assisted Solution

PatHartman earned 125 total points
ID: 40564715
How a date is formatted is separate from how it is stored.  Both Access and SQL Server (and Excel also), store dates as double precision numbers.  The difference is in what each uses as the origin date - the zero value.  Access uses 12/30/1899 as the zero date.  The other two use different dates as the 0 date but the ODBC driver keeps it all straight.  Dates after that are positive numbers.  Today is 42026.  Dates earlier than 12/30/1899 are negative.  The decimal part of the number is the time since midnight.

You also need to be aware that if you format a date, it no longer works like a data.  It works like a string so 01/01/2015 is earlier than 01/10/2014.  Because "01" is less than "10".  Therefore NEVER format the date you are searching/sorting.  When you have a date that is a string, you may need to convert it to mm/dd/yyyy or yyyy/mm/dd format for the query to recognize it as a date and convert it internally to an integer value.  The ## tell SQL that the "string" is actually to be treated as if it were a date.
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 125 total points
ID: 40564851
You have "doubled" the #-marks and you must use the date field as is.
This will work:

If DCount("*", "tblBranchsSpecialDayOff", "[DayOff] = " & SQLDate(dteStartDate) & " AND [BranchNumber] = " & IntBranchNumber & "") = 1 Then    'NOT Holiday

LVL 46

Expert Comment

by:Martin Liss
ID: 40627800
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

810 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