Time not updating

[David]

Hi I am using a query to update different types of data.
for some reason I just can't get past this

for the code below everything works except the ReportTime, ETD and ETA
fields. It keeps coming up with error 13 type mismatch
I have tried putting those cases everywhere and even buy themselves but
nothing is working

Private Sub bttn_DOIT_Click()
On Error GoTo err_bttn_DOIT_Click
Dim sqlString As String
Dim TableRef As String
Dim WhereRef As String
Dim ChangeRef As String

TableRef = "Pax." & Me.combo_Field
WhereRef = "Pax.FlightNo=" & Me.txt_FlightNo & " AND Pax.FlightDate=#" &
Me.txt_Date & "#"
If Me.check_Destination = True Then
WhereRef = WhereRef + " AND Pax.Destination='" &
Me.combo_Destination & "'"
End If

Select Case Me.combo_Field
Case "FlightNo"
ChangeRef = Me.txt_ChangeTo
Case "Destination"
ChangeRef = "'" & Me.txt_ChangeTo & "'"
Case "FlightDate" Or "ReportTime" Or "ETD" Or "ETA"
ChangeRef = "#" & Me.txt_ChangeTo & "#"
End Select


sqlString = "UPDATE Pax SET " & TableRef & "=" & ChangeRef & " WHERE " &
WhereRef & ";"

DoCmd.RunSQL sqlString

exit_bttn_DOIT_Click:
Exit Sub
err_bttn_DOIT_Click:
MsgBox "ThisError " & Err.Number & Err.Description
Resume exit_bttn_DOIT_Click
End Sub

what am I doing wrong??
David

 

[John W. Vinson]

what am I doing wrong??

Hard to tell without seeing your data. Try setting a breakpoint in the code,
running it, and typing

?strSQL

in the Immediate window after it's been defined to see the actual value being
executed. The error suggests that you're missing a delimiter or using the
wrong delimiter - are you certain of the datatypes of all the fields?

John W. Vinson [MVP]

 

[David]

Hello

I have been on the totally wrong path.
I just commented out mt error reporting and found out the that line causeing
trouble is:

Case "FlightDate" Or "ReportTime" Or "ETD" Or "ETA"

when I change it one case per line:
Case "ReportTime"
Case "ETD"
etc
It moves on.
is there a way to have multiples cases on one line? since all of those need
to be handled in the same way it would be good not to have to do it the long
way.

David

 

[Stuart McCall]

Hello

I have been on the totally wrong path.
I just commented out mt error reporting and found out the that line
causeing
trouble is:

Case "FlightDate" Or "ReportTime" Or "ETD" Or "ETA"

when I change it one case per line:
Case "ReportTime"
Case "ETD"
etc
It moves on.
is there a way to have multiples cases on one line? since all of those
need
to be handled in the same way it would be good not to have to do it the
long
way.

Sure, just replace Or with a comma.

 

[David W. Fenton]

Case "FlightDate" Or "ReportTime" Or "ETD" Or "ETA"

when I change it one case per line:
Case "ReportTime"
Case "ETD"
etc
It moves on.
is there a way to have multiples cases on one line? since all of
those need to be handled in the same way it would be good not to
have to do it the long way.

Case "FlightDate", "ReportTime", "ETD", "ETA"

What you had was a Boolean choice, which evaluates completely
differently. When a Select Case is like this:

SELECT CASE "FlightDate"
CASE "FlightDate" Or "ReportTime" Or "ETD" Or "ETA"
...
END SELECT

What happens when this is executed is the expression after SELECT
CASE is compared to each CASE, so the first one is evaluted as:

"FlightDate" = ("FlightDate" Or "ReportTime" Or "ETD" Or "ETD")

So, you're comparing a string value to a Boolean value, and thus it
will never return true.

When you separate the values by commas, it will do this comparison:

"FlightDate"= "FlightDate" OR
"FlightDate"= "ReportTime" OR
"FlightDate"= "ETD" OR
"FlightDate"= "ETD"

It's very similar to the way Application.BuildCriteria works when
constructing WHERE clauses.

If you ask it:

strWhere = Application.BuildCriteria("MyTable.MyDate", dbDate, _
"1/1/2007 OR 3/1/2007")

The value assigned to strWhere will be:

MyTable.MyDate=#1/1/2007# Or MyDate=#3/1/2007#

which can be plugged right into a WHERE clause.

The CASE SELECT does a similar thing behind the scenes in terms of
successive comparisons of multiple values, though without the OR
operators but with the comma, instead.

 

[David]

Thanks Guys
Learning stuff all the time.
Works a charm!