SQL formatting

[cheekybuddha]

Follow @Gasman 's eternal advice:

Put your criteria into a string variable and examine it before passing to the DCount().

(Ah I see Pat has suggested the same in Post #7 )

 

[Design by Sue]

Yep that is one thing but still in red

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.[Text42]
strDateCompleted = Me.DateCompleted
strTimeCompleted = Me.TimeCompleted

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



CurrentDb.Execute strSQL, dbFailOnError

 

[Design by Sue]

I think this is closer - fixed the formatting to match date and double (I think) but still there is s typo

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

 

[Design by Sue]

Follow @Gasman 's eternal advice:

Put your criteria into a string variable and examine it before passing to the DCount().

(Ah I see Pat has suggested the same in Post #7 )

I did that and got the original problem to work - thank you - this is a new problem and the criteria is in a string variable isn't it?

 

[Gasman]

I think this is closer - fixed the formatting to match date and double (I think) but still there is s typo

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

Well if you debug.printed it that would show?, and even if you cannot see it, you could paste back here and someone else could spot it for you.

Not posting within code tags does not help either?
You have a starting single quote for SopNumber?, numbers dod not need quotes, but even if it is text there is no closing quote?

Again, all found with a simple Debug.Print strSQL !!!

 

[cheekybuddha]

After you have loaded your strSQL variable add a Debug.Print strSQL line.

Once the code has run the value in strSQL will be output in the Immediate Window (Ctrl + G)

You can copy and paste that output here.

Also, what datatype is field TimeCompleted?

 

[Design by Sue]

Thanks everyone for all of your help - I am going to try to do this through a query rather than try to update the old code. Hopefully I can get this to work

 

[Gasman]

You are doing it already through a query, just dynamic sql statement?, and you STILL have to get the syntax correct.?

May as well learn to do it correctly now?, else you will just be back in a short while with the same problem?

This might help if you try another way?

Post in thread 'Insert Into Not Working'

Jul 15, 2024

A simpler and more robust solution is to use a temp QueryDef...

    Const SQL_INSERT As String = _
        "Insert Into tblFileRequestData " & _
            "(File_Number, File_Name, File_Title, File_Requested_By, File_Requested_Date, File_Data_Entry_Date, File_Status) " & _
        "Values " & _
            "( p0, p1, p2, p3, p4, p5, p6);"
    
    With CurrentDb.CreateQueryDef("", SQL_INSERT)
        .Parameters(0) = flnum
        .Parameters(1) = flnam
        .Parameters(2) = fltitle
        .Parameters(3) = usern
        .Parameters(4) = flreqdate
        .Parameters(5) =...

• MarkK

• 
• 

 

[Pat Hartman]

@Design by Sue You are way off base with your solution.

NEVER EVER store dates and times as strings. Never store numeric values as strings. Please read my earlier explanation of why. Just because you think your update might be working, doesn't mean you're not in for a whole rash of problems when you try to use string dates and numbers.

 

[Design by Sue]

Ok so I thought I had this working but it is actually not - there are no values in the immediate window and I get the message box every time. I would have thought if there were no matches in the table the code would go to the else and beep but instead but it always gives the message. It is not checking the values in the table. I am about to pull my hair out. I just don't understand what I am doing wrong. Why doesn't this code check the table for matching fields and only proceed. I am truly sorry to keep posting but I just don't know what to do.


Dim strNumber As String
Dim strVersion As String
Dim strEmpNumber As Double
Dim strDateCompleted As Date
Dim srtWhere As String

strNumber = Me.[SOP Number]
strVersion = Me.Version
strEmpNumber = Me.[EmpNumber]
strDateCompleted = Me.DateCompleted
strTWhere = ("[SOP Number]= '" & (strNumber) & "' And [Employee Number]='" & strEmpNumber & "' And SOPVersion='" & strVersion & "' AND [DateCompleted]= #" & (strDateCompleted) & "#")
Debug.Print

If DCount("*", "[Main TBL]", strWhere) > 0 Then
MsgBox "This training has already been added to the database for this employee."

MsgBox "This training has already been added to the database for this employee."

Else: DoCme.Beep
Exit Sub
End If



End If

 

[Pat Hartman]

You are really confused regarding how to use the various data types.

Delimiters:
* Strings are ALWAYS delimited. Use either single or double quotes.
* Dates formatted as strings are always delimited. Use the #
Numbers are NEVER delimited.

* When you reference form controls in a saved querydef (not from embedded SQL in your VBA), you don't need to delimit anything since Access knows enough to do it for you. When you create a query using VBA, the query itself is a string and strings cannot hold objects so all the fields referenced are strings and therefore must be properly delimited.

Save yourself some code and directly reference form fields rather than first moving them to intermediate variables. The only reason to take the intermediate step is if you are going to reference the form controls multiple times each. Referencing a form control is more "expensive" than referencing a locally defined variable. So copying form controls you are only going to reference once wastes time but copying form controls to variables saves time if they will be referenced multiple times, such is in a loop for example.

And finally, make sure you have the fields correctly defined in the tables. You keep waffling in the code you display but we have no idea what their real data types are.

 

[Design by Sue]

You are really confused regarding how to use the various data types.

Delimiters:
* Strings are ALWAYS delimited. Use either single or double quotes.
* Dates formatted as strings are always delimited. Use the #
Numbers are NEVER delimited.

* When you reference form controls in a saved querydef (not from embedded SQL in your VBA), you don't need to delimit anything since Access knows enough to do it for you. When you create a query using VBA, the query itself is a string and strings cannot hold objects so all the fields referenced are strings and therefore must be properly delimited.

Save yourself some code and directly reference form fields rather than first moving them to intermediate variables. The only reason to take the intermediate step is if you are going to reference the form controls multiple times each. Referencing a form control is more "expensive" than referencing a locally defined variable. So copying form controls you are only going to reference once wastes time but copying form controls to variables saves time if they will be referenced multiple times, such is in a loop for example.

And finally, make sure you have the fields correctly defined in the tables. You keep waffling in the code you display but we have no idea what their real data types are.

Yes I know I have gotten this totally confusing. I am going back to my previous code that I thought I had working. I should have put them in separate posts to avoid confusion on the board, And yes I am totally messed up regarding the data types and how to code them. I have also tried to set this up in a query and still get no results. I just don't understand this any more. Appreciate your trying to help. Is it the data types that is causing the code not to see the table info?

 

[Pat Hartman]

Please, Please read what I have written and act on it.

1. Fix the data types in the tables.
2. Use the proper data type in your variables
3. Use the proper delimiters
4. Format dates correctly when you are using them as strings as you are when you build SQL strings using VBA
5. Build your strings into variables so they are easy to examine once built.

 

[Design by Sue]

Please, Please read what I have written and act on it.

1. Fix the data types in the tables.
2. Use the proper data type in your variables
3. Use the proper delimiters
4. Format dates correctly when you are using them as strings as you are when you build SQL strings using VBA
5. Build your strings into variables so they are easy to examine once built.

I am trying to to that - really - but I just am not understanding how to write the code

These are the correct data types
Dim strNumber As String
Dim strVersion As String
Dim strEmpNumber As Double
Dim strDateCompleted As Date
Dim srtWhere As String

At this point I am so lost - I just don't know what to do. I just need this code to work so I can finish the project and get it out of here. I appreciate your help but at this point I just can't see what I am doing wrong with the delimiters.

 

[Gasman]

Sue, you need to debug.print something, in your case strTWhere

 

[Isaac]

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.

Not to get too picky but technically, since the ESC programmer did not write:

Function ESC(str as String) as String

... that means the passed in value need not necessarily be a string. (although I agree it would seem silly to pass nonstring values into it)..

For example, this actually works and does not err:

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

Sub foo()
Dim str
str = CDate("01/01/2001")
MsgBox ESC(str)

End Sub

 

[Design by Sue]

Sue, you need to debug.print something, in your case strTWhere

That's the problem - the debug/print is blank

 

[Design by Sue]

Not to get too picky but technically, since the ESC programmer did not write:

Function ESC(str as String) as String

... that means the passed in value need not necessarily be a string. (although I agree it would seem silly to pass nonstring values into it)..

For example, this actually works and does not err:

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

Sub foo()
Dim str
str = CDate("01/01/2001")
MsgBox ESC(str)

End Sub

Thanks!

 

[theDBguy]

At this point I am so lost - I just don't know what to do.

Would you be open to a screen sharing session?

 

[Design by Sue]

Would you be open to a screen sharing session?

Possibly - I have made some progress in that I have the correct information displaying when I mouse over the codes in this section

strNumber = Me.ComboSOPNo
strVersion = Me.Version
strEmpNumber = Me.[Text42]
strDateCompleted = Me.DateCompleted
strTWhere = ("[SOP Number]= '" & (strNumber) & "' And [Employee Number]='" & strEmpNumber & "' And SOPVersion='" & strVersion & "' AND [DateCompleted]= #" & (strDateCompleted) & "#")

So the information is getting through to that point. Is my DCount code wrong?

If DCount("*", "[Main TBL]", strWhere) > 0 Then
MsgBox "This training is already entered"
Else
MsgBox "This training is not entered."

UPDATE
When I mouse over the srtWhere in the DCount is shows strWhere = Empty