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.)
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)
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...
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
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?
Code:
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.
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?
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.
Code:
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.
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?
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.
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.
Code:
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
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.
Code:
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
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.
Code:
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.
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.
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