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

fixing the sql error in vba access

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. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            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 .
0
drtopserv
Asked:
drtopserv
4 Solutions
 
SimonCommented:
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:
DCount("[mailingdate]","[dbo_LC_Mailshots]","[mailingdate]<#5/7/2014#")
=21
DCount("[mailingdate]","[dbo_LC_Mailshots]","[mailingdate]<#7/5/2014#")
=26

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

0
 
Jeffrey CoachmanMIS LiasonCommented:
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

JeffCoachman
0
 
PatHartmanCommented:
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.
0
 
Gustav BrockCIOCommented:
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

/gustav
0
 
Martin LissRetired ProgrammerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Industry Leaders: 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!

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