Sql issue in ms access (1 Viewer)

Gint32 · Jan 21, 2018

I am trying to amend my sql statement to include another field with no success, my sql skills are just about nil so hopefully someone else may be able to throw a little light on how to. Heres what I have originally and I have commented out the sql field that I need to add to the sql statement as it doesn't work and throws a syntax error when uncommented

Code:

[FONT=Times New Roman][FONT=Times New Roman][SIZE=3]  strSql = "UPDATE Attend SET"
[/SIZE][/FONT][SIZE=3][FONT=Times New Roman]strSql = strSql & " Attend.AttType = " & TypeAttend & ","
strSql = strSql & " Attend.LoggedOnUser = '" & Me![txtUserId] & "',"
strSql = strSql & " Attend.LastAmendedDate = #" & Now() & "#"[/FONT][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][FONT=Times New Roman]''''strSql = strSql & " PrevVal_TypeAttend =  " & PrevVal_TypeAttend & ""
strSql = strSql & " WHERE Attend.AttStudent = " & Me![scrStudent] & " AND"
strSql = strSql & " Attend.AttDate = #" & TDate & "#;"


'  amended/added below on
'  ..21 Jan 2018  G.McGinty

'strSql = strSql & " PrevVal_TypeAttend =  " & PrevVal_TypeAttend & ""

End If[/FONT][/SIZE][/FONT]

The working sql from Debug.Print strSql gives

Code:

[FONT=Times New Roman][SIZE=3][FONT=Times New Roman]'UPDATE Attend SET Attend.AttType = 2, Attend.LoggedOnUser = 'gerry', Attend.LastAmendedDate = #21/01/2018 4:26:11 PM# WHERE Attend.AttStudent = 219477 AND Attend.AttDate = #21/01/2018#;[/FONT][/SIZE][/FONT]
[FONT=Times New Roman][FONT=Times New Roman][SIZE=3]DoCmd.RunSQL strSql[/SIZE][/FONT][/FONT]

Many thanks for reading Gerry from Australia

Gasman · Jan 21, 2018

I'm not to sure where you are trying to put it, in the SET statement or the WHERE statement.?

If set, you need a comma at the start of the string?
if where, you need AND at the start of the string?

I'd also be putting the comma, AND etc at the start of the additional strings to save complicating the code?

HTH

Gint32 said:
I am trying to amend my sql statement to include another field with no success, my sql skills are just about nil so hopefully someone else may be able to throw a little light on how to. Heres what I have originally and I have commented out the sql field that I need to add to the sql statement as it doesn't work and throws a syntax error when uncommented

Code:

[FONT=Times New Roman][FONT=Times New Roman][SIZE=3] strSql = "UPDATE Attend SET" [/SIZE][/FONT][SIZE=3][FONT=Times New Roman]strSql = strSql & " Attend.AttType = " & TypeAttend & "," strSql = strSql & " Attend.LoggedOnUser = '" & Me![txtUserId] & "'," strSql = strSql & " Attend.LastAmendedDate = #" & Now() & "#"[/FONT][/SIZE][/FONT] [FONT=Times New Roman][SIZE=3][FONT=Times New Roman]''''strSql = strSql & " PrevVal_TypeAttend = " & PrevVal_TypeAttend & "" strSql = strSql & " WHERE Attend.AttStudent = " & Me![scrStudent] & " AND" strSql = strSql & " Attend.AttDate = #" & TDate & "#;" ' amended/added below on ' ..21 Jan 2018 G.McGinty 'strSql = strSql & " PrevVal_TypeAttend = " & PrevVal_TypeAttend & "" End If[/FONT][/SIZE][/FONT]

The working sql from Debug.Print strSql gives

Code:

[FONT=Times New Roman][SIZE=3][FONT=Times New Roman]'UPDATE Attend SET Attend.AttType = 2, Attend.LoggedOnUser = 'gerry', Attend.LastAmendedDate = #21/01/2018 4:26:11 PM# WHERE Attend.AttStudent = 219477 AND Attend.AttDate = #21/01/2018#;[/FONT][/SIZE][/FONT] [FONT=Times New Roman][FONT=Times New Roman][SIZE=3]DoCmd.RunSQL strSql[/SIZE][/FONT][/FONT]

Many thanks for reading Gerry from Australia

isladogs · Jan 21, 2018

You are missing a comma separating 2 fields at the end of a line
Corrected version with added comma in RED:

Code:

strSql = "UPDATE Attend SET"
strSql = strSql & " Attend.AttType = " & TypeAttend & ","
strSql = strSql & " Attend.LoggedOnUser = '" & Me![txtUserId] & "',"
strSql = strSql & " Attend.LastAmendedDate = #" & Now() & "#[COLOR="Red"],[/COLOR]"
strSql = strSql & " PrevVal_TypeAttend =  " & PrevVal_TypeAttend & ""
strSql = strSql & " WHERE Attend.AttStudent = " & Me![scrStudent] & " AND"
strSql = strSql & " Attend.AttDate = #" & TDate & "#;"

However as Gasman has already said, its too easy to make mistakes setting out SQL like this (hopefully I haven't)
In my view, the version below is both easier to write & to understand

Code:

strSql = "UPDATE Attend" & _
	" SET Attend.AttType = " & TypeAttend, Attend.LoggedOnUser = '" & Me![txtUserId] & "'," & _
	" Attend.LastAmendedDate = #" & Now() & "#, PrevVal_TypeAttend = " & PrevVal_TypeAttend & "" & _
        " WHERE Attend.AttStudent = " & Me![scrStudent] AND Attend.AttDate = #" & TDate & "#;"

Gint32 · Jan 22, 2018

ridders said:
You are missing a comma separating 2 fields at the end of a line
Corrected version with added comma in RED:

Code:

strSql = "UPDATE Attend SET" strSql = strSql & " Attend.AttType = " & TypeAttend & "," strSql = strSql & " Attend.LoggedOnUser = '" & Me![txtUserId] & "'," strSql = strSql & " Attend.LastAmendedDate = #" & Now() & "#[COLOR=Red],[/COLOR]" strSql = strSql & " PrevVal_TypeAttend = " & PrevVal_TypeAttend & "" strSql = strSql & " WHERE Attend.AttStudent = " & Me![scrStudent] & " AND" strSql = strSql & " Attend.AttDate = #" & TDate & "#;"

However as Gasman has already said, its too easy to make mistakes setting out SQL like this (hopefully I haven't)
In my view, the version below is both easier to write & to understand

Code:

strSql = "UPDATE Attend" & _ " SET Attend.AttType = " & TypeAttend, Attend.LoggedOnUser = '" & Me![txtUserId] & "'," & _ " Attend.LastAmendedDate = #" & Now() & "#, PrevVal_TypeAttend = " & PrevVal_TypeAttend & "" & _ " WHERE Attend.AttStudent = " & Me![scrStudent] AND Attend.AttDate = #" & TDate & "#;"

Thanks For your suggestions

Although, the first set of amended code you supplied gives me your about to update 0 records
and the second preferred tells me that there is a compile error and stops on the comma after (TypeAttend,)..So not sure which to play with as both don't run.

isladogs · Jan 22, 2018

Gint32 said:
Thanks For your suggestions

Although, the first set of amended code you supplied gives me your about to update 0 records
and the second preferred tells me that there is a compile error and stops on the comma after (TypeAttend,)..So not sure which to play with as both don't run.

Brilliant!!! (NOT!)
BUT there is no comma after TypeAttend in the second (preferred) code
Can you do a debug.print on that ...if it lets you
Then compare withy the SQL from the first code

As for the first code giving no records, suggest you post the SQL into the query designer then change it to design view.
Does it look correct?

Gint32 · Jan 22, 2018

Code:

  strSql = "UPDATE Attend SET"
  strSql = strSql & " Attend.AttType = " & TypeAttend & ","
  strSql = strSql & " Attend.LoggedOnUser = '" & Me![txtUserId] & "',"
  strSql = strSql & " Attend.LastAmendedDate = #" & Now() & "#,"
  strSql = strSql & " PrevVal_TypeAttend =  " & PrevVal_TypeAttend & ","
  strSql = strSql & " WHERE Attend.AttStudent = " & Me![scrStudent] & " AND"
  strSql = strSql & " Attend.AttDate = #" & TDate & "#;"

Code:

 UPDATE Attend SET Attend.AttType = 3, Attend.LoggedOnUser = 'gerry', Attend.LastAmendedDate = #22/01/2018 2:37:18 PM#, PrevVal_TypeAttend =  2, WHERE Attend.AttStudent = 219477 AND Attend.AttDate = #21/01/2018#;

Debug From the above give me a message says syntax error
">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

And pasting the below in, well as I said it stops at the comma & TypeAttend,
and tells me compile error expected "end of statement"
So I can't run the below to debug it

Code:

strSql = "UPDATE Attend" & _
    " SET Attend.AttType = " & TypeAttend, Attend.LoggedOnUser = '" & Me![txtUserId] & "'," & _
    " Attend.LastAmendedDate = #" & Now() & "#, PrevVal_TypeAttend = " & PrevVal_TypeAttend & "" & _
        " WHERE Attend.AttStudent = " & Me![scrStudent] AND Attend.AttDate = #" & TDate & "#;"

So I guess after messing around in query Design & Sql view, I am looking for something like the following only with my needed variables, the below will update one record when tested, but obviously the below does contain the required variables, so how the convert the below is the issue, since I am specifying actual dates and values:

Code:

UPDATE Attend SET Attend.[AttType] = 3, Attend.[LoggedOnUser] = "gerry", Attend.[LastAmendedDate] = #1/22/2018 14:43:22#, Attend.[PrevVal_TypeAttend] = 2 Where  Attend.AttStudent = 219477 AND Attend.AttDate = #21/01/2018#

To a working version of :-

Code:

strSql = "UPDATE Attend" & " SET Attend.AttType = " & TypeAttend, Attend.LoggedOnUser = '" & _
Me![txtUserId] & "'," & " Attend.LastAmendedDate = #" & Now() & "#, PrevVal_TypeAttend = " & PrevVal_TypeAttend & "" & _
" WHERE Attend.AttStudent = " & Me![scrStudent] AND Attend.AttDate = #" & TDate & "#;"

...thanks for all your patience.

JHB · Jan 22, 2018

Are you sure Me![txtUserId] gives you a name, (then ID could indicate a number)?
Another problem, you've to convert the TDate to US format (mm/dd/yyyy), you can do it using the Format function.
Then you've forgotten a & " .

Code:

" WHERE Attend.AttStudent = " & Me![scrStudent] [B][COLOR=Red]& "[/COLOR][/B] ....

Else post you database with some sample data, zip it, + a description how to run your problem code.

isladogs · Jan 22, 2018

Gint32

The problem is that you ALTERED the code I supplied.

If you read both versions of the code I posted there is NO COMMA between Preval_TypeAttend and WHERE. You added one so got an error.

Commas are used to separate the list of fields but not at the end of the list

Suggest you COPY AND PASTE the first code I gave you.

Gint32 · Jan 22, 2018

JHB said:
Are you sure Me![txtUserId] gives you a name, (then ID could indicate a number)?
Another problem, you've to convert the TDate to US format (mm/dd/yyyy), you can do it using the Format function.
Then you've forgotten a & " .

Code:

" WHERE Attend.AttStudent = " & Me![scrStudent] [B][COLOR=Red]& "[/COLOR][/B] ....

Else post you database with some sample data, zip it, + a description how to run your problem code.

thanks for looking, yes Me![txtUserId] will always give a name as it will always be the logged on user.

Gint32 · Jan 22, 2018

ridders said:
Gint32

The problem is that you ALTERED the code I supplied.

If you read both versions of the code I posted there is NO COMMA between Preval_TypeAttend and WHERE. You added one so got an error.

Commas are used to separate the list of fields but not at the end of the list

Suggest you COPY AND PASTE the first code I gave you.

Thanks for that, I change the comma in both versions, so the first now works as you suggested, as follows

Code:

  strSql = "UPDATE Attend SET"
  strSql = strSql & " Attend.AttType = " & TypeAttend & ","
  strSql = strSql & " Attend.LoggedOnUser = '" & Me![txtUserId] & "',"
  strSql = strSql & " Attend.LastAmendedDate = #" & Now() & "#,"
  strSql = strSql & " PrevVal_TypeAttend =  " & PrevVal_TypeAttend
  strSql = strSql & " WHERE Attend.AttStudent = " & Me![scrStudent] & " AND"
  strSql = strSql & " Attend.AttDate = #" & TDate & "#;"

But this (preferred) still stops at the comma..with expected end of line error

Code:

strSql = "UPDATE Attend" & _     " SET Attend.AttType = " & TypeAttend, Attend.LoggedOnUser = '" & Me![txtUserId] & "'," & _     " Attend.LastAmendedDate = #" & Now() & "#, PrevVal_TypeAttend = " & PrevVal_TypeAttend & "" & _         " WHERE Attend.AttStudent = " & Me![scrStudent] AND Attend.AttDate = #" & TDate & "#;"

Gint32 · Jan 22, 2018

ridders said:
Gint32

The problem is that you ALTERED the code I supplied.

If you read both versions of the code I posted there is NO COMMA between Preval_TypeAttend and WHERE. You added one so got an error.

Commas are used to separate the list of fields but not at the end of the list

Suggest you COPY AND PASTE the first code I gave you.

Thanks Ridders, I have done as you suggested so now the first now works as it should, but the preferred one liner version still has the same is with the comma and expected end of line error as you see this is your orginal:

Code:

strSql = "UPDATE Attend" & _ 	" SET Attend.AttType = " & TypeAttend, Attend.LoggedOnUser = '" & Me![txtUserId] & "'," & _ 	" Attend.LastAmendedDate = #" & Now() & "#, PrevVal_TypeAttend = " & PrevVal_TypeAttend & "" & _         " WHERE Attend.AttStudent = " & Me![scrStudent] AND Attend.AttDate = #" & TDate & "#;"

isladogs · Jan 22, 2018

Sorry I made a mistake in the SECOND code.
Corrected version below

Code:

strSql = "UPDATE Attend" & _
    " SET Attend.AttType = " & TypeAttend, Attend.LoggedOnUser = '" & Me![txtUserId] & "'," & _
    " Attend.LastAmendedDate = #" & Now() & "#, PrevVal_TypeAttend = " & PrevVal_TypeAttend & "" & _
        " WHERE Attend.AttStudent = " & Me![scrStudent][COLOR="Red"] & "[/COLOR] AND Attend.AttDate = #" & TDate & "#;"

or this should also work as the [] are only needed where the field contains a space or a special character

Code:

strSql = "UPDATE Attend" & _
    " SET Attend.AttType = " & TypeAttend, Attend.LoggedOnUser = '" & Me.txtUserId & "'," & _
    " Attend.LastAmendedDate = #" & Now() & "#, PrevVal_TypeAttend = " & PrevVal_TypeAttend & "" & _
        " WHERE Attend.AttStudent = " & Me.scrStudent[COLOR="red"] & "[/COLOR] AND Attend.AttDate = #" & TDate & "#;"

Hopefully error free this time!

Gint32 · Jan 22, 2018

ridders said:

Sorry I made a mistake in the SECOND code.
Corrected version below

Code:

strSql = "UPDATE Attend" & _
    " SET Attend.AttType = " & TypeAttend, Attend.LoggedOnUser = '" & Me![txtUserId] & "'," & _
    " Attend.LastAmendedDate = #" & Now() & "#, PrevVal_TypeAttend = " & PrevVal_TypeAttend & "" & _
        " WHERE Attend.AttStudent = " & Me![scrStudent][COLOR="Red"] & "[/COLOR] AND Attend.AttDate = #" & TDate & "#;"

or this should also work as the [] are only needed where the field contains a space or a special character

Code:

strSql = "UPDATE Attend" & _
    " SET Attend.AttType = " & TypeAttend, Attend.LoggedOnUser = '" & Me.txtUserId & "'," & _
    " Attend.LastAmendedDate = #" & Now() & "#, PrevVal_TypeAttend = " & PrevVal_TypeAttend & "" & _
        " WHERE Attend.AttStudent = " & Me.scrStudent[COLOR="red"] & "[/COLOR] AND Attend.AttDate = #" & TDate & "#;"

Hopefully error free this time!

Nope and sorry to say its still the same error with both versions. But it ok the not preferred works though...thank

isladogs · Jan 22, 2018

Oops - I screwed up AGAIN.... should have checked the first (or second) time around

Code:

strSql = "UPDATE Attend" & _
    " SET Attend.AttType = " & TypeAttend[COLOR="Red"] & "[/COLOR], Attend.LoggedOnUser = '" & Me.txtUserId & "'," & _
    " Attend.LastAmendedDate = #" & Now() & "#, PrevVal_TypeAttend = " & PrevVal_TypeAttend & "" & _
        " WHERE Attend.AttStudent = " & Me.scrStudent & " AND Attend.AttDate = #" & TDate & "#;"

And because I wanted this to be the final version, I even tested it ...

Code:

Sub TestSQL2()
Dim TypeAttend As Integer, txtUserID As String, TDate As Date, scrStudent As Long, PrevVal_TypeAttend As Long
TypeAttend = 2
txtUserID = "gerry"
TDate = #1/21/2018#
scrStudent = 219477
PrevVal_TypeAttend = 100
dteDate = #11/21/2017#

strSQL = "UPDATE Attend" & _
    " SET Attend.AttType = " & TypeAttend & ", Attend.LoggedOnUser = '" & txtUserID & "'," & _
    " Attend.LastAmendedDate = #" & Now() & "#, PrevVal_TypeAttend = " & PrevVal_TypeAttend & "" & _
        " WHERE Attend.AttStudent = " & scrStudent & " AND Attend.AttDate = #" & TDate & "#;"
Debug.Print strSQL
End Sub

which gave me:

Code:

UPDATE Attend SET Attend.AttType = 2, Attend.LoggedOnUser = 'gerry', Attend.LastAmendedDate = #22/01/2018 10:49:50#, PrevVal_TypeAttend = 100 WHERE Attend.AttStudent = 219477 AND Attend.AttDate = #21/01/2018#;

Gint32 · Jan 22, 2018

ridders said:

Oops - I screwed up AGAIN.... should have checked the first (or second) time around

Code:

strSql = "UPDATE Attend" & _
    " SET Attend.AttType = " & TypeAttend[COLOR=red] & "[/COLOR], Attend.LoggedOnUser = '" & Me.txtUserId & "'," & _
    " Attend.LastAmendedDate = #" & Now() & "#, PrevVal_TypeAttend = " & PrevVal_TypeAttend & "" & _
        " WHERE Attend.AttStudent = " & Me.scrStudent & " AND Attend.AttDate = #" & TDate & "#;"

And because I wanted this to be the final version, I even tested it ...

Code:

Sub TestSQL2()
Dim TypeAttend As Integer, txtUserID As String, TDate As Date, scrStudent As Long, PrevVal_TypeAttend As Long
TypeAttend = 2
txtUserID = "gerry"
TDate = #1/21/2018#
scrStudent = 219477
PrevVal_TypeAttend = 100
dteDate = #11/21/2017#

strSQL = "UPDATE Attend" & _
    " SET Attend.AttType = " & TypeAttend & ", Attend.LoggedOnUser = '" & txtUserID & "'," & _
    " Attend.LastAmendedDate = #" & Now() & "#, PrevVal_TypeAttend = " & PrevVal_TypeAttend & "" & _
        " WHERE Attend.AttStudent = " & scrStudent & " AND Attend.AttDate = #" & TDate & "#;"
Debug.Print strSQL
End Sub

which gave me:

Code:

UPDATE Attend SET Attend.AttType = 2, Attend.LoggedOnUser = 'gerry', Attend.LastAmendedDate = #22/01/2018 10:49:50#, PrevVal_TypeAttend = 100 WHERE Attend.AttStudent = 219477 AND Attend.AttDate = #21/01/2018#;

Yep, thanks very much for your hard earned efforts, as it works now!

isladogs · Jan 22, 2018

You're welcome. Pity I didn't get it right first time!

Gasman · Jan 22, 2018

Search this site for SQL2VBA as Ridders created a program to easily swap between languages.

pbaldy · Jan 22, 2018

Post 10 was moderated, now approved. I'm posting to trigger email notifications.

Sql issue in ms access (1 Viewer)

Gint32

Registered User.

Gasman

Enthusiastic Amateur

isladogs

MVP / VIP

Gint32

Registered User.

isladogs

MVP / VIP

Gint32

Registered User.

JHB

Have been here a while

isladogs

MVP / VIP

Gint32

Registered User.

Gint32

Registered User.

Gint32

Registered User.

isladogs

MVP / VIP

Gint32

Registered User.

isladogs

MVP / VIP

Gint32

Registered User.

isladogs

MVP / VIP

Gasman

Enthusiastic Amateur

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread