Solved

fixing the sql error in vba access

Posted on 2015-01-22
6
242 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
[X]
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
6 Comments
 
LVL 18

Accepted Solution

by:
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:
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 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

JeffCoachman
0
 
LVL 37

Assisted Solution

by:PatHartman
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.
0
 
LVL 50

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

/gustav
0
 
LVL 47

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

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!

Question has a verified solution.

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

Suggested Solutions

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

734 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