We help IT Professionals succeed at work.

Update Query

HI

I am trying to write an update query so that I can update the same fields with the click of a button from a form.
Giving me a syntax error
Update SETTLEMENT Set SETTLEMENT.Meetings = Forms!SETTLEMENTQuery!Meetings WHERE SETTLEMENT.RepName = fOSUserName() And SETTLEMENT.SDate = Forms!SETTLEMENTQuery!SDate

Open in new window


Thanks
Su
Comment
Watch Question

Commented:
What's fOSUserName()?  A user defined function?

Author

Commented:
Yes it is a user defined function

Author

Commented:
HI guys
I have updated the query still below
strSQL = "UPDATE [SETTLEMENT] SET [Meetings]=" & Nz(Me.Meetings.Value, 0) & "WHERE [SDate] =" & Me.SDate.Value _
  And "[RepName]=" & Me.RepName.Value
  

Open in new window

Still giving the error "Type mismatch"

Thanks
Raj ShekharSr. Tech Arch.

Commented:
Hi,

Please check that all concatenated value should return string value.

If it is other then string, then type mismatch error will be coming.
ste5anSenior Developer
CERTIFIED EXPERT
Commented:
When [SDate] is a Date/Time value, then you need to format the value as US Date/Time string. See Allen Browne's SQLDate() function. Also there is an error in the string building and line continuation. E.g.

strSQL = "UPDATE [SETTLEMENT] " & _
  "SET [Meetings] = " & Nz(Me.Meetings.Value, 0) & " " & _
  "WHERE [SDate] = " & SQLDate(Me.SDate.Value) & " " & _
  "AND [RepName] = '" & Me.RepName.Value & "';"

Open in new window

Take care of the spaces. Use
MsgBox strSql

Open in new window

to display the SQL statement. Copy it into a new empty query to test it.

Author

Commented:
Hi ste5an

There is a message syntax error coming

syntax error(missing operator) in query expression '[SDate] = AND [RepName] ='JameRo"

Thanks
Senior Developer
CERTIFIED EXPERT
Commented:
Take a look at the content of Me.SDate.Value. This control should not be empty and thus not be NULL. Also it should contain a date/time value, not an arbitrary string.

Author

Commented:
Thanks a lot for your help

Explore More ContentExplore courses, solutions, and other research materials related to this topic.