SQL formatting

[Design by Sue]

Hopefully this is a simple question - can someone please point me to the instructions on formatting (hope that is the correct word) SQL for date, text and number fields. So when referring to a field that is a date field using the #? (not sure that is even correct) I googled but can't find what I know is available as I have had it inthe past.)

 

[cheekybuddha]

In Access SQL:

Numbers are not delimited.

Strings are delimited by either a single quote or double quote (IMHO, single quote is preferable since it is less easily confused with VBA string quotes, and is more compatible with other database system string delimiters.

Dates are delimited by octothorphes/hash marks (#) - when passing a date into an SQL statement it must also be in an unambiguous format which translates as either yyyy-mm-dd (ISO date format) or mm/dd/yyyy (US date format)

 

[MajP]

Thread 'Get Filter from Controls, CSQL, and Combine Multiple Filters'

May 19, 2020

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require a lot of work up front, but in the long run it makes building applications so much easier.
Probably the biggest area I see people struggle and often painfully, is writing proper SQL to use in filters or other SQL code. The biggest issue is how to properly delimit literal values and pulling this...

• MajP
• Replies: 19
• Forum: Modules & VBA

• 

 

[Design by Sue]

I thought I posted this in a new thread but can'f find it so I am asking here because it follows through with my original post. Can someone please tell me what I have wrong in this statement. I know it has to do with the past part, somewhere near the AND '{DateCompleted]. I am trying to add this final criteria to an existing code that works but I can't see my error. (I am using this code as a test on a button before I apply it to the actual final code that i need to run but I need to get this part working first. I greatly appreciate any assistance. I used to understand this formatting but it has been years.


Dim strNumber As String
Dim strVersion As String
Dim strEmpNumber As String
Dim strDateCompleted As String

strNumber = Me.[SOP Number]
strVersion = Me.Version
strEmpNumber = Me.[Employee Number]
strDateCompleted = Me.DateCompleted

If DCount("*", "[Main TBL]", "[SOP Number]= '" & ESC(strNumber) & "' And [Employee Number]='" & strEmpNumber & "' And SOPVersion=" & strVersion & "’ AND “[DateCompleted]= '" # ESC(strDateCompleted) # "')> 0 Then
DoCmd.Beep
End If

 

[theDBguy]

Maybe it's just a type when copying the code to the post, but it looks like you're missing a single quote after SOPVersion. In addition to that, there's a bunch of single quotes around the DateCompleted part that are not necessary. Maybe try the following?

If DCount("*", "[Main TBL]", "[SOP Number]= '" & ESC(strNumber) & "' And [Employee Number]='" & strEmpNumber & "' And SOPVersion='" & strVersion & "' AND [DateCompleted]= #" &  ESC(strDateCompleted) & "#")> 0 Then

PS. Not sure what ESC() does, but it's possible you might not need to use # as well.

 

[Design by Sue]

Thak you theDBguy That is not accepted by access but I get a Data Type mismatch. The DateCompleted is a date. The code before this works so there much be something in that part that is not correct. Any thoughts?

 

[Pat Hartman]

Bookmark these functions and keep the link handy.
Format function specifically:

Format Function - Microsoft Support

support.microsoft.com

All functions by Category:

Access Functions (by category) - Microsoft Support

Find Help with Access functions in this categorical list.

support.microsoft.com

I don't know what Esc() is. Did you create this function yourself?

One way to make evaluating concatenated strings easier is to build the string into a variable first.

Dim strWhere AS String
strWhere = "[SOP Number]= '" & ESC(strNumber) & "' And [Employee Number]='" & strEmpNumber & "' And SOPVersion=" & strVersion & "’ AND “[DateCompleted]= '" # ESC(strDateCompleted) # "')
Debug.Print strWhere
If DCount("*", "[Main TBL]", strWhere)> 0 Then
    DoCmd.Beep
End If

Once you see the string, you can usually see the error. If you can't, you can open the QBE and create a query that uses the string as the where clause and that might get you a better error message.

When using date strings in SQL, the format of the date must be either the US standard of mm/dd/yyyy or the unambiguous yyyy/mm/dd.

 

[theDBguy]

Thak you theDBguy That is not accepted by access but I get a Data Type mismatch. The DateCompleted is a date. The code before this works so there much be something in that part that is not correct. Any thoughts?

Can you show us the code for the ESC() function?

 

[Design by Sue]

This is something that the previous programmer added

Function ESC(str) As String
ESC = Replace(str, "'", "''")
End Function

 

[theDBguy]

This is something that the previous programmer added

Function ESC(str) As String
ESC = Replace(str, "'", "''")
End Function

Type mismatch means we don't know the data types of your fields. Can you tell us what they are?

 

[Design by Sue]

EmployeeNumber is Text
SOPNumber is Text
SOPVersion is Number
DateCompleted is Date/Time

This is the info from the table

 

[Pat Hartman]

String functions work on strings. Numbers and Dates are not strings and would cause a type error. Remove the code where it is not needed. The function was probably created to handle names like O'Brian which have embedded quotes. This function doubles the single quote so it will not be interpreted as the start or end of a string.

 

[Design by Sue]

Thank you
So I am understanding the following are incorrect, what should they be?

Dim strEmpNumber As String
Dim strDateCompleted As String

I tried


Dim strEmpNumber As Integer
Dim strDateCompleted As Date

But still the error

I am sorry but I just can't get this
Thanks for the help

Also tried Single and Double for EmpNumber

 

[theDBguy]

EmployeeNumber is Text
SOPNumber is Text
SOPVersion is Number
DateCompleted is Date/Time

This is the info from the table

Okay, assuming EmployeeNumber, SOPNumber, or SOPVersion would never have single quotes in them, maybe you could try the following just to see if the error goes away.

If DCount("*", "[Main TBL]", "[SOP Number]= '" & Me.[SOP Number] & "' And [Employee Number]='" & Me.[Employee Number] & "' And SOPVersion='" & Me.Version & "' AND [DateCompleted]= #" Format(Me.DateCompleted, "yyyy-mm-dd") & "#")> 0 Then

 

[Design by Sue]

Okay, assuming EmployeeNumber, SOPNumber, or SOPVersion would never have single quotes in them, maybe you could try the following just to see if the error goes away.

If DCount("*", "[Main TBL]", "[SOP Number]= '" & Me.[SOP Number] & "' And [Employee Number]='" & Me.[Employee Number] & "' And SOPVersion='" & Me.Version & "' AND [DateCompleted]= #" Format(Me.DateCompleted, "yyyy-mm-dd") & "#")> 0 Then

That is in red so no - thanks

 

[theDBguy]

That is in red so no - thanks

Oh, I think I missed one &.

If DCount("*", "[Main TBL]", "[SOP Number]= '" & Me.[SOP Number] & "' And [Employee Number]='" & Me.[Employee Number] & "' And SOPVersion='" & Me.Version & "' AND [DateCompleted]= #" & Format(Me.DateCompleted, "yyyy-mm-dd") & "#")> 0 Then

 

[Design by Sue]

Bookmark these functions and keep the link handy.
Format function specifically:

Format Function - Microsoft Support

support.microsoft.com

All functions by Category:

Access Functions (by category) - Microsoft Support

Find Help with Access functions in this categorical list.

support.microsoft.com

I don't know what Esc() is. Did you create this function yourself?

One way to make evaluating concatenated strings easier is to build the string into a variable first.

Dim strWhere AS String
strWhere = "[SOP Number]= '" & ESC(strNumber) & "' And [Employee Number]='" & strEmpNumber & "' And SOPVersion=" & strVersion & "’ AND “[DateCompleted]= '" # ESC(strDateCompleted) # "')
Debug.Print strWhere
If DCount("*", "[Main TBL]", strWhere)> 0 Then
    DoCmd.Beep
End If

Once you see the string, you can usually see the error. If you can't, you can open the QBE and create a query that uses the string as the where clause and that might get you a better error message.

When using date strings in SQL, the format of the date must be either the US standard of mm/dd/yyyy or the unambiguous yyyy/mm/dd.

Thank you for this Pat - I just reread this and think I am making headway!

 

[Pat Hartman]

So I am understanding the following are incorrect, what should they be?

Dim strEmpNumber As String
Dim strDateCompleted As String

You can't just redefine data as some other type. That also changes its properties. For example 10 is > 9 if you are working with fields defined as numeric. However 9> 10 when the field is a string. Why? Strings compare/sort looking at the characters from left to right so 1 is < 9.

 

[Design by Sue]

Thank you all - I got that to work!!! YAY but now another formatting problem. Trying to use what I now have for the above and create code to update the table by changing the DateComplete and TimeCompleted to match the information on the form if the table already has a record where the EmployeeNumber, SOPNumber and SOP Versions match. The strSQL statement is in red so once again I have a typo I am sure. (hopefully not something bigger) Once again I beg of you to help me solve this problem. (I think I am so close to getting this form to work I am hoping with this answer I will be on my way!)


Private Sub UpdateDate()

Dim strNumber As String
Dim strVersion As String
Dim strEmpNumber As Double
Dim strDateCompleted As Date
Dim strTimeCompleted As String
Dim strSQL As String

strNumber = Me.[SOP Number]
strVersion = Me.Version
strEmpNumber = Me.[EmpNumber]
strDateCompleted = Me.DateCompleted
strTimeCompleted = Me.TimeCompleted

strSQL = "Update [Main TBL] Set [DateCompleted]= #" & (strDateCompleted) & "# And TimeCompleted = "#" & (Me.TimeCompleted) & "# Where [Employee Number] = " & strEmpNumber & " And [SOP Number] = "[SOP Number]= " & (strNumber) & " And SOPVersion = " & strVersion & ""


CurrentDb.Execute strSQL, dbFailOnError

 

[theDBguy]

" And [SOP Number] = "[SOP Number]= "

Something is off here...