SQL compile error

[Walter]

I have a text box on a form to display the last odometer
entered for a specific truck based on the truck #
selected. I created a set of queries to select the
appropriate record. I pasted the SQL from the last query
into the AfterUpdate event of the Truck # control to set
the Control Source of the text box to this query. When I
go to form view and choose a truck I get Compile Error,
Syntax Error with the SQL code highlighted. Can someone
tell me what is wrong with my code.
Also, I am not sure this is the proper way to display
this information. I tried setting the Control Source to
the odometer field of the query(which seems to me to be
the same thing I am trying to do here) and requerying the
text box in the AfterUpdate event of the Truck # box but
I get ?Name.
Any help will will be greatly appreciated,
Walter

 

[Walter]

-----Original Message-----
I have a text box on a form to display the last odometer
entered for a specific truck based on the truck #
selected. I created a set of queries to select the
appropriate record. I pasted the SQL from the last query
into the AfterUpdate event of the Truck # control to set
the Control Source of the text box to this query. When I
go to form view and choose a truck I get Compile Error,
Syntax Error with the SQL code highlighted. Can someone
tell me what is wrong with my code.

I forgot to post my code.
Private Sub TruckID_AfterUpdate()
Me.LastOdometer.ControlSource = _
"SELECT Odometer " _
"FROM qryTripEnd2 " _
"INNER JOIN tblTripDetails " _
"ON qryTripEnd2.MaxOfOdometer =
tblTripDetails.Odometer; "

 

[Dirk Goldgar]

I forgot to post my code.
Private Sub TruckID_AfterUpdate()
Me.LastOdometer.ControlSource = _
"SELECT Odometer " _
"FROM qryTripEnd2 " _
"INNER JOIN tblTripDetails " _
"ON qryTripEnd2.MaxOfOdometer =
tblTripDetails.Odometer; "

You can't set a text box's ControlSource property to a SQL statement;
or rather, you can do it but it will give you a run-time error in the
control. *IF* you could, you would need to use the concatenation
operator '&' to piece together the separate strings that you are
assembling into the controlsource string:

Me.LastOdometer.ControlSource = _
"SELECT Odometer " & _
"FROM qryTripEnd2 " & _
"INNER JOIN tblTripDetails " & _
"ON qryTripEnd2.MaxOfOdometer = " & _
"tblTripDetails.Odometer; "


*BUT*, as I said above, that won't work anyway. However, if you have a
stored query with the SQL above, then you can set the text box's
ControlSource to a DLookup function expression that retrieves the
information. Fort example, if that query were named "qryLastOdometer",
then you might have this as the (static) ControlSource expression of the
text box:

=DLookup("Odometer", "qryLastOdometer")

and then requery the text box in TruckID_AfterUpdate:

Private Sub TruckID_AfterUpdate()

Me.LastOdometer.Requery

End Sub

 

[Walter]

Thanks Dirk. I don't know what you mean by "static" but
I set the control source to the field returned by the
query as you described. I had tried this previously,
using the expression builder which used brackets instead
of quotes and I got the #Name? error. One small problem
though is I am getting different data with this method.
If I select a truck on the form, I get an incorrect
odometer reading. However, if I open the query and
supply the same truck #, I get a different odometer
reading which is the correct one from the table. Any
thoughts on what may be causing this?
I assume the same process would work with validation
rules. Odometer validation rule: >=DLookup
("Odometer", "qryLastOdometer")?
Thanks so much for your help. I have been pulling my
hair out trying to get this working.
Walter

 

[Dirk Goldgar]

Thanks Dirk. I don't know what you mean by "static"

I meant that you would set the ControlSource at design time, and not
change it on the fly, as you were doing before.

but
I set the control source to the field returned by the
query as you described. I had tried this previously,
using the expression builder which used brackets instead
of quotes and I got the #Name? error.

The expression builder isn't very bright, and will let you build
expressions that might be correct in some circumstances but not in the
one where you're trying to use it. You're much better off learning more
about Access so you can type in your own expressions.

One small problem
though is I am getting different data with this method.

That's a *big* problem, not a small one!

If I select a truck on the form, I get an incorrect
odometer reading. However, if I open the query and
supply the same truck #, I get a different odometer
reading which is the correct one from the table. Any
thoughts on what may be causing this?

You did place the code I suggested to requery the text box in the
AfterUpdate event of the TruckID control? Assuming you did, and it's
still not giving you the correct odometer reading, please post the SQL
of each of the queries involved: qryLastOdometer (if that's what it's
called), qryTripEnd2, and any query that may be used by qryTripEnd2.

I assume the same process would work with validation
rules. Odometer validation rule: >=DLookup
("Odometer", "qryLastOdometer")?
Thanks so much for your help. I have been pulling my
hair out trying to get this working.

I think that should work, provided the validation rule is set for a
control on a form -- it wouldn't work for a field in table design view.
But I'm not 100% sure, so let's fix the problem with the LastOdometer
text box first.

 

[Walter]

Thanks again. Yes I have code to requery the
LastOdometer control:
Private Sub TruckID_AfterUpdate()
Me.LastOdometer.Requery
Me.LastLocation.Requery
Me.LastState.Requery

There are 3 queries involved:
qryTripEnd1;
SELECT tblTrips.TripID, tblTrips.TruckID,
tblTripDetails.MileageDescription, tblTripDetails.Odometer
FROM tblTrips INNER JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
WHERE (((tblTrips.TruckID)=[Forms]![frmTrips]![truckID])
AND ((tblTripDetails.MileageDescription)="Trip End"));

qryTripEnd2;
SELECT qryTripEnd1.TruckID,
qryTripEnd1.MileageDescription, Max
(tblTripDetails.Odometer) AS MaxOfOdometer
FROM qryTripEnd1 INNER JOIN tblTripDetails ON
(qryTripEnd1.MileageDescription =
tblTripDetails.MileageDescription) AND
(qryTripEnd1.TripID = tblTripDetails.TripID)
GROUP BY qryTripEnd1.TruckID,
qryTripEnd1.MileageDescription;

qryTripEnd3;
SELECT qryTripEnd2.TruckID, tblTripDetails.Odometer,
tblTripDetails.Location, tblTripDetails.State
FROM qryTripEnd2 INNER JOIN tblTripDetails ON
qryTripEnd2.MaxOfOdometer = tblTripDetails.Odometer;

There are 3 text boxes used here; Odometer, Location,
State. The set of queries includes all these fields and
I can treat each the same way.
Walter

 

[Dirk Goldgar]

Thanks again. Yes I have code to requery the
LastOdometer control:
Private Sub TruckID_AfterUpdate()
Me.LastOdometer.Requery
Me.LastLocation.Requery
Me.LastState.Requery

There are 3 queries involved:
qryTripEnd1;
SELECT tblTrips.TripID, tblTrips.TruckID,
tblTripDetails.MileageDescription, tblTripDetails.Odometer
FROM tblTrips INNER JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
WHERE (((tblTrips.TruckID)=[Forms]![frmTrips]![truckID])
AND ((tblTripDetails.MileageDescription)="Trip End"));

qryTripEnd2;
SELECT qryTripEnd1.TruckID,
qryTripEnd1.MileageDescription, Max
(tblTripDetails.Odometer) AS MaxOfOdometer
FROM qryTripEnd1 INNER JOIN tblTripDetails ON
(qryTripEnd1.MileageDescription =
tblTripDetails.MileageDescription) AND
(qryTripEnd1.TripID = tblTripDetails.TripID)
GROUP BY qryTripEnd1.TruckID,
qryTripEnd1.MileageDescription;

qryTripEnd3;
SELECT qryTripEnd2.TruckID, tblTripDetails.Odometer,
tblTripDetails.Location, tblTripDetails.State
FROM qryTripEnd2 INNER JOIN tblTripDetails ON
qryTripEnd2.MaxOfOdometer = tblTripDetails.Odometer;

There are 3 text boxes used here; Odometer, Location,
State. The set of queries includes all these fields and
I can treat each the same way.

What are the fields and keys of tblTrips and tblTripDetails, and how are
they related? I'm not sure your queries are all correct (if I
understand what you're trying to get from them), but I'm not sure they
aren't, either. Can you post a few sample records that I can use to
test these out?

 

[Walter]

I created the queries from the design view as described
in one of the KB articles. I couldn't grasp writing the
SQL. Here are the tables I have.
tblTrips:
TripID AutoNumber PK
TripDate DAte/Time
TruckID Text FK to trucks table
TrailerID Text FK to trailers table
EmployeeID Number FK to employees table

tblTripDetails:
TripDetailsID AutoNumber PK
TripID Number FK to Trips table
Odometer Text
MileageDescription Text FK to O/DDesc. table
State Text FK to States table
StopPurpose Text FK to Stops table
Gallons Text Fuel purchased
Amount Currency Cost of fuel
Location Text Stop/Trip End City

Here are some records.
Date 1/20/04
Trip # 84
Truck # 9406
TripDetailsID 72 73 74 ... 77 78
Odometer 425550 425600 425700 426100 426300
Mile/Descrip. Fuel St Line St Line St Line Trip End
State NC TN SC
StopPurpose
Gallons 125
Amount $250.00
Location Lexington

Date 2/1/04
Trip # 85
Truck # 9406
TripDetailsID 79 80 81 82
Odometer 426450 426500 426600 426800
Mile/Descrip. Fuel St Line St Line Trip End
State NC TN
StopPurpose
Gallons 125
Amount $250.00
Location Loudon

Date 2/20/04
Trip # 86
Truck # 9406
TripDetailsID 83 84 85
Odometer 427000 427100 427300
Mile/Descrip. St Line St Line Trip End
State NC SC
StopPurpose
Gallons
Amount
Location Lexington

Date 2/25/04
Trip # 87
Truck # 9406
TripDetailsID 86 87 88 ... 94 95
Odometer 427325 427500 427800 428385 428585
Mile/Descrip. Stop Stop Fuel St Line Trip End
State SC
StopPurpose Unload Load
Gallons 250
Amount $500.00
Location Pelion Latta Lexington

Date 3/15/04
Trip # 88
Truck # 9406
TripDetailsID 96 97 ... 100 101 102
Odometer 428750 428800 429300 429385 429500
Mile/Descrip. Fuel st Line St Line St Line Trip end
State SC NC SC
StopPurpose
Gallons 250
Amount $500.00
Location Lexington

Date 11/1/03
Trip # 57
Truck # 209
TripDetailsID 52 53 54 55
Odometer 405100 405150 405200 405250
Mile/Descrip. St Line Fuel Stop Trip End
State SC
StopPurpose Unload
Gallons 150
Amount $300.00
Location Lexington

Date 12/1/03
Trip # 67
Truck # 209
TripDetailsID 60 61 62 63
Odometer 405350 405500 405515 405650
Mile/Descrip. Stop ST Line Fuel Trip End
State NC
StopPurpose Load
Gallons 75
Amount $150.00
Location Lexington

Date 01/15/04
Trip # 80
Truck # 209
TripDetailsID 64 65 66 67 68
Odometer 405675 405700 405800 405900 405975
Mile/Descrip. ST Line Fuel Stop St Line Trip End
State GA SC
StopPurpose Unl/Rel
Gallons 75
Amount $150.00
Location Madison Lexington

Date 3/15/04
Trip # 98
Truck # 209
TripDetailsID 108 ... 111 112 113 114
Odometer 406000 406300 406500 406585 406785
Mile/Descrip. Fuel Stop St Line St Line Trip End
State NC SC
StopPurpose Unl/Rel
GAllons 125
Amount $250.00
Location Loudon Lexington

I hope this makes sense to you.
Thanks,
Walter

-----Original Message-----

Thanks again. Yes I have code to requery the
LastOdometer control:
Private Sub TruckID_AfterUpdate()
Me.LastOdometer.Requery
Me.LastLocation.Requery
Me.LastState.Requery

There are 3 queries involved:
qryTripEnd1;
SELECT tblTrips.TripID, tblTrips.TruckID,
tblTripDetails.MileageDescription, tblTripDetails.Odometer
FROM tblTrips INNER JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
WHERE (((tblTrips.TruckID)=[Forms]![frmTrips]! [truckID])
AND ((tblTripDetails.MileageDescription)="Trip End"));

qryTripEnd2;
SELECT qryTripEnd1.TruckID,
qryTripEnd1.MileageDescription, Max
(tblTripDetails.Odometer) AS MaxOfOdometer
FROM qryTripEnd1 INNER JOIN tblTripDetails ON
(qryTripEnd1.MileageDescription =
tblTripDetails.MileageDescription) AND
(qryTripEnd1.TripID = tblTripDetails.TripID)
GROUP BY qryTripEnd1.TruckID,
qryTripEnd1.MileageDescription;

qryTripEnd3;
SELECT qryTripEnd2.TruckID, tblTripDetails.Odometer,
tblTripDetails.Location, tblTripDetails.State
FROM qryTripEnd2 INNER JOIN tblTripDetails ON
qryTripEnd2.MaxOfOdometer = tblTripDetails.Odometer;

There are 3 text boxes used here; Odometer, Location,
State. The set of queries includes all these fields and
I can treat each the same way.

What are the fields and keys of tblTrips and tblTripDetails, and how are
they related? I'm not sure your queries are all correct (if I
understand what you're trying to get from them), but I'm not sure they
aren't, either. Can you post a few sample records that I can use to
test these out?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[Dirk Goldgar]

I created the queries from the design view as described
in one of the KB articles. I couldn't grasp writing the
SQL. Here are the tables I have. [...]
I hope this makes sense to you.

Hmm. I built the tables and queries, loaded the tables with the data
you supplied, made an unbound form "frmTrips" with a combo box for
TruckID and a text box for LastOdometer, with the controlsource of
LastOdometer set to

=DLookUp("Odometer","qryTripEnd3")

I set this AfterUpdate event procedure for TruckID:

Private Sub TruckID_AfterUpdate()
Me!LastOdometer.Requery
End Sub

and I tested it out. It worked as I expected, and returned the same
results as running qryTripEnd3 directly, depending of course on which
TruckID was selected in the combo box at the time. So I'm not sure
what's wrong with your setup.

I do see one or two possible problems, but I don't see how they would
cause your DLookup to give different results from the query itself.

Problem 1: Your Odometer field is defined as text, not number, and so
odometer readings will be compared as text, not numbers. That means,
for example, that an odometer reading of "9" will compare as greater
than one of "100000". If your odometer readings are always entered as
six digits with leading zeros, this won't be a problem, but otherwise it
certainly would be.

Problem 2: Your queries will pull the maximum odometer reading, based
on the assumption that this represents the last trip a truck made. If
an odometer could be reset or could roll over (is that conceivable?
probably not), then the maximum odometer reading might not be the last
trip. If you need an alternative -- it may well be that you don't --
you could identify the last trip as the one with the maximum date.

Anyway, I don't see where the discrepancy you're seeing could be coming
from. If you can verify that you are indeed getting such a discrepancy,
maybe I need to see the actual database. If it's small enough, you can
send it to me if you'd like. "Small enough" means that the database --
or a cut-down copy of it -- can be compacted and zipped to 1MB or less.
If it works out that way, you can send it to the address you get by
removing NO SPAM from the reply-address of this message.

 

[Walter]

Thanks for looking at this. I'm sorry it I was unable to
get back sooner. I think I may have a computer problem
that was causing the error. I think I need to add some
memory because if I have several things open while
working on Access, sometimes when I go from design view
to form view after making changes Access closes and I
have to reopen it.
I changed the odometer and also the gallons to number. I
now am getting the correct data each time I try it. Yes,
the odometer can roll over so I may need to consider Max
Date. Thanks for the suggestion. I hadn't thought of
that.
Unfortunately, I am now having problems with code to
Show/Hide controls on my subform that was working
properly and I haven't knowingly changed anything.
I am trying to validate the trip date to >= last trip
entered. The TripDate is the first control with the
TruckID next. I have the following code in the TruckID
Exit event.
Private Sub TruckID_Exit(Cancel As Integer)
If "DLookup('TripDate','qryLastTripDate')" >
Me.TripDate Then
MsgBox "The last trip entered for this truck was
on " _
& "DLookup('TripDate', 'qryLastTripDate')" _
& vbCrLf & "Please enter a date greater than or
equal to this date.", , _
"Invalid Date Entry"
Me.TripDate.SetFocus
Else
Me.Driver.Requery
Me.LastOdometer.Requery
Me.LastLocation.Requery
Me.LastState.Requery
End If
End Sub
The MsgBox is coming up even with a valid date. It also
displays the literal
DLookup('TripDate', 'qryLastTripDate'). If I remove the
quotes, it only displays
"The last trip entered for this truck was on " and the OK
button. The prompt indicates ( ) but if I use them I get
an Expected = error.
If you could take a moment to look at this and tell me
how to correct it would be great. Many thanks for your
help.
Walter

-----Original Message-----

I created the queries from the design view as described
in one of the KB articles. I couldn't grasp writing the
SQL. Here are the tables I have. [...]
I hope this makes sense to you.

Hmm. I built the tables and queries, loaded the tables with the data
you supplied, made an unbound form "frmTrips" with a combo box for
TruckID and a text box for LastOdometer, with the controlsource of
LastOdometer set to

=DLookUp("Odometer","qryTripEnd3")

I set this AfterUpdate event procedure for TruckID:

Private Sub TruckID_AfterUpdate()
Me!LastOdometer.Requery
End Sub

and I tested it out. It worked as I expected, and returned the same
results as running qryTripEnd3 directly, depending of course on which
TruckID was selected in the combo box at the time. So I'm not sure
what's wrong with your setup.

I do see one or two possible problems, but I don't see how they would
cause your DLookup to give different results from the query itself.

Problem 1: Your Odometer field is defined as text, not number, and so
odometer readings will be compared as text, not numbers. That means,
for example, that an odometer reading of "9" will compare as greater
than one of "100000". If your odometer readings are always entered as
six digits with leading zeros, this won't be a problem, but otherwise it
certainly would be.

Problem 2: Your queries will pull the maximum odometer reading, based
on the assumption that this represents the last trip a truck made. If
an odometer could be reset or could roll over (is that conceivable?
probably not), then the maximum odometer reading might not be the last
trip. If you need an alternative -- it may well be that you don't --
you could identify the last trip as the one with the maximum date.

Anyway, I don't see where the discrepancy you're seeing could be coming
from. If you can verify that you are indeed getting such a discrepancy,
maybe I need to see the actual database. If it's small enough, you can
send it to me if you'd like. "Small enough" means that the database --
or a cut-down copy of it -- can be compacted and zipped to 1MB or less.
If it works out that way, you can send it to the address you get by
removing NO SPAM from the reply-address of this message.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[Dirk Goldgar]

Thanks for looking at this. I'm sorry it I was unable to
get back sooner. I think I may have a computer problem
that was causing the error. I think I need to add some
memory because if I have several things open while
working on Access, sometimes when I go from design view
to form view after making changes Access closes and I
have to reopen it.

Hmm. That may reflect a corruption of your database, or some problem
with your Access installation, or even a problem with your PC. It isn't
supposed to crash just because you're using a lot of virtual memory.

I changed the odometer and also the gallons to number. I
now am getting the correct data each time I try it.

That sounds good, at least.

Yes,
the odometer can roll over so I may need to consider Max
Date. Thanks for the suggestion. I hadn't thought of
that.

You're welcome.

Unfortunately, I am now having problems with code to
Show/Hide controls on my subform that was working
properly and I haven't knowingly changed anything.
I am trying to validate the trip date to >= last trip
entered. The TripDate is the first control with the
TruckID next. I have the following code in the TruckID
Exit event.
Private Sub TruckID_Exit(Cancel As Integer)
If "DLookup('TripDate','qryLastTripDate')" >
Me.TripDate Then
MsgBox "The last trip entered for this truck was
on " _
& "DLookup('TripDate', 'qryLastTripDate')" _
& vbCrLf & "Please enter a date greater than or
equal to this date.", , _
"Invalid Date Entry"
Me.TripDate.SetFocus
Else
Me.Driver.Requery
Me.LastOdometer.Requery
Me.LastLocation.Requery
Me.LastState.Requery
End If
End Sub
The MsgBox is coming up even with a valid date. It also
displays the literal
DLookup('TripDate', 'qryLastTripDate'). If I remove the
quotes, it only displays
"The last trip entered for this truck was on " and the OK
button. The prompt indicates ( ) but if I use them I get
an Expected = error.
If you could take a moment to look at this and tell me
how to correct it would be great. Many thanks for your
help.

It's really an unrelated question that should have been asked as the
start of a separate thread. However, I feel compelled to point out that
this code could not possibly ever have worked as you posted it. Your
use of quotes around DLookup is the main issue, but the code really
should be split up and placed half in the BeforeUpdate event of TruckID,
and half in the AfterUpdate event of TruckID -- not really in the Exit
event at all. Like this:

'----- start of suggested code -----
Private Sub TruckID_BeforeUpdate(Cancel As Integer)

Dim dtLastTrip As Date

dtLastTrip = DLookup("TripDate","qryLastTripDate")

If dtLastTrip > Me.TripDate Then

MsgBox _
"The last trip entered for this truck was on " & _
dtLastTrip & vbCrLf & _
"Please enter a date greater than or equal " & _
"to this date.", , _
"Invalid Date Entry"

Cancel = True
End If

End Sub


Private Sub TruckID_AfterUpdate()

Me.Driver.Requery
Me.LastOdometer.Requery
Me.LastLocation.Requery
Me.LastState.Requery

End Sub
'----- end of suggested code -----

 

[Walter]

Thanks again for all your help. I've seen some threads
concerning corrupt db's. I'll check them out to see if
this is my problem. You have been a great help.
Walter