Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

fixing the sql error in vba access

Posted on 2015-01-22
6
Medium Priority
?
250 Views
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. 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
Comment
Question by:drtopserv
6 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 500 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:
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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 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

JeffCoachman
0
 
LVL 40

Assisted Solution

by:PatHartman
PatHartman earned 500 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.
0
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 500 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

/gustav
0
 
LVL 49

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.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

916 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