Solved

fixing the sql error in vba access

Posted on 2015-01-22
6
236 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 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 36

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

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

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.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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 …

821 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