Solved Error 2023 & 3075 relating to SQL Statement (1 Viewer)

Jason Lee Hayes

Active member
Local time
Today, 19:59
Joined
Jul 25, 2020
Messages
174
I am using some code that simply populated a table when a form is opened and closed which populated some fields. I am trying to insert the current value of a TempVar declaired as [TempVars]!TempLogonName] to the field UserName however i cannot seem to get the formatting write. The TempVar holds a String Value. I thing i may have not enough or too many Speech Marks in the line below but being playing with it and tried many versions including usning it through some of those free online SQL checkers but non identify the error. Any help would be very much appreciated... Thanks in advance..

If i take the following bit out it works some likely be something with this bit I'm sure:-

Code:
  AND (tblDataChanges.UserName = """ & [TempVars]![TempLogonName] &  """)"

My TempVar Value is returning what is expected (The name of the current user) eg "Joe Bloggs" and my TempVar works everywhere else...

Code:
  strSql = "SELECT tblDataChanges.* FROM tblDataChanges WHERE ((tblDataChanges.DocTypeID = " & lngObjType & ") AND (tblDataChanges.DocName = """ & strDoc & ") AND (tblDataChanges.UserName = """ & [TempVars]![TempLogonName] & _
            """) AND (tblDataChanges.DocHWnd = " & lngHWnd & ") AND (tblDataChanges.CloseDateTime Is Null) AND (tblDataChanges.OpenDateTime <= Now())) ORDER BY tblDataChanges.OpenDateTime, tblDataChanges.LogID;"
 

Attachments

  • Screen Shot 10-24-21 at 08.39 PM 001.PNG
    Screen Shot 10-24-21 at 08.39 PM 001.PNG
    89.7 KB · Views: 358
  • Screen Shot 10-24-21 at 08.39 PM.PNG
    Screen Shot 10-24-21 at 08.39 PM.PNG
    80.4 KB · Views: 320

Gasman

Enthusiastic Amateur
Local time
Today, 19:59
Joined
Sep 21, 2011
Messages
14,047
Your docname has no closing quote?
Put it all into string variable and debug.print that until the syntax is correct.
 

Jason Lee Hayes

Active member
Local time
Today, 19:59
Joined
Jul 25, 2020
Messages
174
Thanks Gasman... I was so focused on the TempVar believing it was this i could not see the wood for the trees....
I should have known really as the value was being returned and stored in the table....
I have corrected this now to the following:-

Code:
  strSql = "SELECT tblDataChanges.* FROM tblDataChanges WHERE ((tblDataChanges.DocTypeID = " & lngObjType & ") AND (tblDataChanges.DocName = """ & strDoc & """) AND (tblDataChanges.UserName = """ & [TempVars]![TempLogonName] & _
            """) AND (tblDataChanges.DocHWnd = " & lngHWnd & ") AND (tblDataChanges.CloseDateTime Is Null) AND (tblDataChanges.OpenDateTime <= Now())) ORDER BY tblDataChanges.OpenDateTime, tblDataChanges.LogID;"

Thankyou
 

Users who are viewing this thread

Top Bottom