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
surah79Asked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
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.
0
 
mlongohCommented:
What's fOSUserName()?  A user defined function?
0
 
surah79Author Commented:
Yes it is a user defined function
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
surah79Author 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
0
 
rajshekherCommented:
Hi,

Please check that all concatenated value should return string value.

If it is other then string, then type mismatch error will be coming.
0
 
ste5anSenior DeveloperCommented:
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.
0
 
surah79Author Commented:
Hi ste5an

There is a message syntax error coming

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

Thanks
0
 
surah79Author Commented:
Thanks a lot for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.