MsgBox problem

[Walter]

I have the following code in a TruckID control to
validate the trip date for each truck. If I enter a date
previous to the last date, the MsgBox appears with "The
last trip entered for this truck was on " and the OK
button. Can someone tell me why it is not showing the
date of the last trip? Also it is displaying the MsgBox
even when a valid date is entered. Thanks to Dirk
Goldgar for the code. I have commented the "As Date"
because with it in place, I would get a "type mismatch"
error and the debug would show "dtLastTrip = 12:00:00 AM"

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

Thanks for your help,
Walter

 

[Ken Snell [MVP]]

Your DLookup code step is not filtering the records in any way. Thus, the
code step will return the "first" record that the function finds in
qryLastTripDate. And if the query returns no records, dtLastTrip will be a
Null value (see my comment below about how you're "dimming" the variable).
And a Null will show as a blank entry in your MsgBox.What is the SQL
statement of qryLastTripDate?

I also note that this code step has commented out the "As Date" portion of
the step; that makes dtLastTrip a Variant data type, not a Date data type.
Is this what you wanted?
Dim dtLastTrip 'As Date

 

[Walter]

-----Original Message-----
Your DLookup code step is not filtering the records in any way. Thus, the
code step will return the "first" record that the function finds in
qryLastTripDate. And if the query returns no records, dtLastTrip will be a
Null value (see my comment below about how

you're "dimming" the variable).

And a Null will show as a blank entry in your MsgBox.What is the SQL
statement of qryLastTripDate?

If I run the query by itself, providing a truck #, it
returns the correct date. Here is the SQL:

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips INNER JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
GROUP BY tblTrips.TruckID
HAVING (((tblTrips.TruckID)=[forms]![frmTrips]!
[TruckID]));

I also note that this code step has commented out the "As Date" portion of
the step; that makes dtLastTrip a Variant data type, not a Date data type.
Is this what you wanted?
Dim dtLastTrip 'As Date

With the "As Date" in the code, I get a Run time
error '13': Type mismatch. I go to debug and the
"Dim dtLastTrip As Date" line is highlighted in yellow.
If I hold the cursor over the "dtLastTrip" it shows a
value of 12:00:00 AM.
Thanks,
Walter

 

[Ken Snell [MVP]]

The data type mismatch, when you declare dtLastTrip as a Date type, is being
caused when the DLookup function finds no records and is returning a Null
value; a Date variable cannot accept a value of Null, so the code errors.
When you comment out the "As Date", then dtLastTrip is declared as a Variant
type, which can accept a Null value. Then, when its value is Null, nothing
displays in your message box because Null has no value, and there is nothing
to display.

The problem is that your DLookup is returning a value of Null in these
cases. I assume that is because the query will return a record * only if *
there is a child record in the tblTripDetails table for the tblTrips.TruckID
value that is in the TruckID control that is on your frmTrips form. If
there is no child record, there is no record returned by the query, even if
you have a record in tblTrips for that TruckID.

Perhaps you need to modify your query:

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips LEFT JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
WHERE tblTrips.TruckID=[forms]![frmTrips]![TruckID]
GROUP BY tblTrips.TruckID;

The above query will return a record if there is a record in tblTrips for
that TruckID * even if * you don't have a child record in the tblTripDetails
table. It also will run a bit faster because I've changed the HAVING filter
to a WHERE filter; WHERE filter is performed before the query groups the
results (thus, nondesired records are filtered out before grouping), whereas
HAVING filters after the grouping is done. Grouping takes a lot of
resources, so it's better to filter beforer grouping if you can do it.
--

Ken Snell
<MS ACCESS MVP>

-----Original Message-----
Your DLookup code step is not filtering the records in any way. Thus, the
code step will return the "first" record that the function finds in
qryLastTripDate. And if the query returns no records, dtLastTrip will be a
Null value (see my comment below about how

you're "dimming" the variable).

And a Null will show as a blank entry in your MsgBox.What is the SQL
statement of qryLastTripDate?

If I run the query by itself, providing a truck #, it
returns the correct date. Here is the SQL:

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips INNER JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
GROUP BY tblTrips.TruckID
HAVING (((tblTrips.TruckID)=[forms]![frmTrips]!
[TruckID]));

I also note that this code step has commented out the "As Date" portion of
the step; that makes dtLastTrip a Variant data type, not a Date data type.
Is this what you wanted?
Dim dtLastTrip 'As Date

With the "As Date" in the code, I get a Run time
error '13': Type mismatch. I go to debug and the
"Dim dtLastTrip As Date" line is highlighted in yellow.
If I hold the cursor over the "dtLastTrip" it shows a
value of 12:00:00 AM.
Thanks,
Walter

--

Ken Snell
<MS ACCESS MVP>






.

 

[Walter]

-----Original Message-----
The data type mismatch, when you declare dtLastTrip as a Date type, is being
caused when the DLookup function finds no records and is returning a Null
value; a Date variable cannot accept a value of Null, so the code errors.
When you comment out the "As Date", then dtLastTrip is declared as a Variant
type, which can accept a Null value. Then, when its value is Null, nothing
displays in your message box because Null has no value, and there is nothing
to display.

The problem is that your DLookup is returning a value of Null in these
cases. I assume that is because the query will return a record * only if *
there is a child record in the tblTripDetails table for the tblTrips.TruckID
value that is in the TruckID control that is on your frmTrips form. If
there is no child record, there is no record returned by the query, even if
you have a record in tblTrips for that TruckID.

All trips in the db have child records in
tblTripDetails.

Perhaps you need to modify your query:

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips LEFT JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
WHERE tblTrips.TruckID=[forms]![frmTrips]![TruckID]
GROUP BY tblTrips.TruckID;

The above query will return a record if there is a record in tblTrips for
that TruckID * even if * you don't have a child record in the tblTripDetails
table. It also will run a bit faster because I've changed the HAVING filter
to a WHERE filter; WHERE filter is performed before the query groups the
results (thus, nondesired records are filtered out before grouping), whereas
HAVING filters after the grouping is done. Grouping takes a lot of
resources, so it's better to filter beforer grouping if you can do it.
--

I pasted your SQL in a blank query window, renamed the
old query and saved the new one as qryLastTripDate. I
then tried to add a new record using a valid date and got
the "Invalid date error" MsgBox with only the first line
of text displayed. I understand it not displaying a null
value but it is also not displaying the second line of
text; "Please enter a date greater than or equal to this
date."
Walter

Ken Snell
<MS ACCESS MVP>

-----Original Message-----
Your DLookup code step is not filtering the records in any way. Thus, the
code step will return the "first" record that the function finds in
qryLastTripDate. And if the query returns no records, dtLastTrip will be a
Null value (see my comment below about how

you're "dimming" the variable).

And a Null will show as a blank entry in your MsgBox.What is the SQL
statement of qryLastTripDate?

If I run the query by itself, providing a truck #, it
returns the correct date. Here is the SQL:

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips INNER JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
GROUP BY tblTrips.TruckID
HAVING (((tblTrips.TruckID)=[forms]![frmTrips]!
[TruckID]));

I also note that this code step has commented out the "As Date" portion of
the step; that makes dtLastTrip a Variant data type,

not
a Date data type.

Is this what you wanted?
Dim dtLastTrip 'As Date

With the "As Date" in the code, I get a Run time
error '13': Type mismatch. I go to debug and the
"Dim dtLastTrip As Date" line is highlighted in yellow.
If I hold the cursor over the "dtLastTrip" it shows a
value of 12:00:00 AM.
Thanks,
Walter

--

Ken Snell
<MS ACCESS MVP>



I have the following code in a TruckID control to
validate the trip date for each truck. If I enter a date
previous to the last date, the MsgBox appears with "The
last trip entered for this truck was on " and the OK
button. Can someone tell me why it is not showing the
date of the last trip? Also it is displaying the MsgBox
even when a valid date is entered. Thanks to Dirk
Goldgar for the code. I have commented the "As Date"
because with it in place, I would get a "type mismatch"
error and the debug would show "dtLastTrip = 12:00:00 AM"

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

Thanks for your help,
Walter


.

.

 

[Ken Snell [MVP]]

Put a breakpoint on the code step with the MsgBox line. When the code breaks
at that point, after you begin adding a new record, put the cursor over the
dtLastTrip variable. What is the value that is shown?

Then, in the Immediate Window, type
?DLookup("TripDate", "qryLastTripDate")

What value is returned?

Also, in the Immediate window, type
?)[forms]![frmTrips]![TruckID]

What value is returned?

What value do you think is in the TruckID control on the form frmTrips?
--

Ken Snell
<MS ACCESS MVP>

-----Original Message-----
The data type mismatch, when you declare dtLastTrip as a Date type, is being
caused when the DLookup function finds no records and is returning a Null
value; a Date variable cannot accept a value of Null, so the code errors.
When you comment out the "As Date", then dtLastTrip is declared as a Variant
type, which can accept a Null value. Then, when its value is Null, nothing
displays in your message box because Null has no value, and there is nothing
to display.

The problem is that your DLookup is returning a value of Null in these
cases. I assume that is because the query will return a record * only if *
there is a child record in the tblTripDetails table for the tblTrips.TruckID
value that is in the TruckID control that is on your frmTrips form. If
there is no child record, there is no record returned by the query, even if
you have a record in tblTrips for that TruckID.

All trips in the db have child records in
tblTripDetails.

Perhaps you need to modify your query:

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips LEFT JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
WHERE tblTrips.TruckID=[forms]![frmTrips]![TruckID]
GROUP BY tblTrips.TruckID;

The above query will return a record if there is a record in tblTrips for
that TruckID * even if * you don't have a child record in the tblTripDetails
table. It also will run a bit faster because I've changed the HAVING filter
to a WHERE filter; WHERE filter is performed before the query groups the
results (thus, nondesired records are filtered out before grouping), whereas
HAVING filters after the grouping is done. Grouping takes a lot of
resources, so it's better to filter beforer grouping if you can do it.
--

I pasted your SQL in a blank query window, renamed the
old query and saved the new one as qryLastTripDate. I
then tried to add a new record using a valid date and got
the "Invalid date error" MsgBox with only the first line
of text displayed. I understand it not displaying a null
value but it is also not displaying the second line of
text; "Please enter a date greater than or equal to this
date."
Walter

Ken Snell
<MS ACCESS MVP>

-----Original Message-----
Your DLookup code step is not filtering the records in
any way. Thus, the
code step will return the "first" record that the
function finds in
qryLastTripDate. And if the query returns no records,
dtLastTrip will be a
Null value (see my comment below about how
you're "dimming" the variable).
And a Null will show as a blank entry in your
MsgBox.What is the SQL
statement of qryLastTripDate?

If I run the query by itself, providing a truck #, it
returns the correct date. Here is the SQL:

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips INNER JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
GROUP BY tblTrips.TruckID
HAVING (((tblTrips.TruckID)=[forms]![frmTrips]!
[TruckID]));

I also note that this code step has commented out
the "As Date" portion of
the step; that makes dtLastTrip a Variant data type, not
a Date data type.
Is this what you wanted?
Dim dtLastTrip 'As Date

With the "As Date" in the code, I get a Run time
error '13': Type mismatch. I go to debug and the
"Dim dtLastTrip As Date" line is highlighted in yellow.
If I hold the cursor over the "dtLastTrip" it shows a
value of 12:00:00 AM.
Thanks,
Walter


--

Ken Snell
<MS ACCESS MVP>



message
I have the following code in a TruckID control to
validate the trip date for each truck. If I enter a
date
previous to the last date, the MsgBox appears with "The
last trip entered for this truck was on " and the OK
button. Can someone tell me why it is not showing the
date of the last trip? Also it is displaying the
MsgBox
even when a valid date is entered. Thanks to Dirk
Goldgar for the code. I have commented the "As Date"
because with it in place, I would get a "type mismatch"
error and the debug would show "dtLastTrip = 12:00:00
AM"

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

Thanks for your help,
Walter


.

.

 

[Walter]

I have never used breakpoints. Can you tell me
specifically how to do this?

-----Original Message-----
Put a breakpoint on the code step with the MsgBox line. When the code breaks
at that point, after you begin adding a new record, put the cursor over the
dtLastTrip variable. What is the value that is shown?

Then, in the Immediate Window, type
?DLookup("TripDate", "qryLastTripDate")

What value is returned?

Also, in the Immediate window, type
?)[forms]![frmTrips]![TruckID]

What value is returned?

What value do you think is in the TruckID control on the form frmTrips?
--

Ken Snell
<MS ACCESS MVP>

-----Original Message-----
The data type mismatch, when you declare dtLastTrip as

a
Date type, is being

caused when the DLookup function finds no records and

is
returning a Null

value; a Date variable cannot accept a value of Null,

so
the code errors.

When you comment out the "As Date", then dtLastTrip is declared as a Variant
type, which can accept a Null value. Then, when its value is Null, nothing
displays in your message box because Null has no value, and there is nothing
to display.

The problem is that your DLookup is returning a value

of
Null in these

cases. I assume that is because the query will return a record * only if *
there is a child record in the tblTripDetails table for the tblTrips.TruckID
value that is in the TruckID control that is on your frmTrips form. If
there is no child record, there is no record returned

by
the query, even if

you have a record in tblTrips for that TruckID.

All trips in the db have child records in
tblTripDetails.

Perhaps you need to modify your query:

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips LEFT JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
WHERE tblTrips.TruckID=[forms]![frmTrips]![TruckID]
GROUP BY tblTrips.TruckID;

The above query will return a record if there is a record in tblTrips for
that TruckID * even if * you don't have a child record in the tblTripDetails
table. It also will run a bit faster because I've changed the HAVING filter
to a WHERE filter; WHERE filter is performed before the query groups the
results (thus, nondesired records are filtered out before grouping), whereas
HAVING filters after the grouping is done. Grouping takes a lot of
resources, so it's better to filter beforer grouping if you can do it.
--

I pasted your SQL in a blank query window, renamed the
old query and saved the new one as qryLastTripDate. I
then tried to add a new record using a valid date and got
the "Invalid date error" MsgBox with only the first line
of text displayed. I understand it not displaying a null
value but it is also not displaying the second line of
text; "Please enter a date greater than or equal to this
date."
Walter

Ken Snell
<MS ACCESS MVP>



-----Original Message-----
Your DLookup code step is not filtering the records in
any way. Thus, the
code step will return the "first" record that the
function finds in
qryLastTripDate. And if the query returns no records,
dtLastTrip will be a
Null value (see my comment below about how
you're "dimming" the variable).
And a Null will show as a blank entry in your
MsgBox.What is the SQL
statement of qryLastTripDate?

If I run the query by itself, providing a truck #, it
returns the correct date. Here is the SQL:

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips INNER JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
GROUP BY tblTrips.TruckID
HAVING (((tblTrips.TruckID)=[forms]![frmTrips]!
[TruckID]));

I also note that this code step has commented out
the "As Date" portion of
the step; that makes dtLastTrip a Variant data type, not
a Date data type.
Is this what you wanted?
Dim dtLastTrip 'As Date

With the "As Date" in the code, I get a Run time
error '13': Type mismatch. I go to debug and the
"Dim dtLastTrip As Date" line is highlighted in yellow.
If I hold the cursor over the "dtLastTrip" it shows a
value of 12:00:00 AM.
Thanks,
Walter


--

Ken Snell
<MS ACCESS MVP>



message
I have the following code in a TruckID control to
validate the trip date for each truck. If I enter a
date
previous to the last date, the MsgBox appears with "The
last trip entered for this truck was on " and the OK
button. Can someone tell me why it is not showing the
date of the last trip? Also it is displaying the
MsgBox
even when a valid date is entered. Thanks to Dirk
Goldgar for the code. I have commented the "As Date"
because with it in place, I would get a "type mismatch"
error and the debug would show "dtLastTrip = 12:00:00
AM"

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

Thanks for your help,
Walter


.



.

.

 

[Walter]

OK. After my last post I was able to display help(could
not display it before) and found out how to use
breakpoints.

-----Original Message-----
Put a breakpoint on the code step with the MsgBox line. When the code breaks
at that point, after you begin adding a new record, put the cursor over the
dtLastTrip variable. What is the value that is shown?

In trying to add a new record with a valid date I still
get the "type Mismatch" error. The dtLastTrip variable
shows 12:00:00 AM

Then, in the Immediate Window, type
?DLookup("TripDate", "qryLastTripDate")

What value is returned? ??

Also, in the Immediate window, type
?)[forms]![frmTrips]![TruckID]

In trying to print this ?)[forms]![frmTrips]![TruckID] my
computer locked up and I had to download an update. I
have to restart so I thought I would send this much. I'll
post the rest after restart.
Thanks,
Walter

What value is returned?

What value do you think is in the TruckID control on the form frmTrips?
--

Ken Snell
<MS ACCESS MVP>

-----Original Message-----
The data type mismatch, when you declare dtLastTrip as

a
Date type, is being

caused when the DLookup function finds no records and

is
returning a Null

value; a Date variable cannot accept a value of Null,

so
the code errors.

When you comment out the "As Date", then dtLastTrip is declared as a Variant
type, which can accept a Null value. Then, when its value is Null, nothing
displays in your message box because Null has no value, and there is nothing
to display.

The problem is that your DLookup is returning a value

of
Null in these

cases. I assume that is because the query will return a record * only if *
there is a child record in the tblTripDetails table for the tblTrips.TruckID
value that is in the TruckID control that is on your frmTrips form. If
there is no child record, there is no record returned

by
the query, even if

you have a record in tblTrips for that TruckID.

All trips in the db have child records in
tblTripDetails.

Perhaps you need to modify your query:

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips LEFT JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
WHERE tblTrips.TruckID=[forms]![frmTrips]![TruckID]
GROUP BY tblTrips.TruckID;

The above query will return a record if there is a record in tblTrips for
that TruckID * even if * you don't have a child record in the tblTripDetails
table. It also will run a bit faster because I've changed the HAVING filter
to a WHERE filter; WHERE filter is performed before the query groups the
results (thus, nondesired records are filtered out before grouping), whereas
HAVING filters after the grouping is done. Grouping takes a lot of
resources, so it's better to filter beforer grouping if you can do it.
--

I pasted your SQL in a blank query window, renamed the
old query and saved the new one as qryLastTripDate. I
then tried to add a new record using a valid date and got
the "Invalid date error" MsgBox with only the first line
of text displayed. I understand it not displaying a null
value but it is also not displaying the second line of
text; "Please enter a date greater than or equal to this
date."
Walter

Ken Snell
<MS ACCESS MVP>



-----Original Message-----
Your DLookup code step is not filtering the records in
any way. Thus, the
code step will return the "first" record that the
function finds in
qryLastTripDate. And if the query returns no records,
dtLastTrip will be a
Null value (see my comment below about how
you're "dimming" the variable).
And a Null will show as a blank entry in your
MsgBox.What is the SQL
statement of qryLastTripDate?

If I run the query by itself, providing a truck #, it
returns the correct date. Here is the SQL:

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips INNER JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
GROUP BY tblTrips.TruckID
HAVING (((tblTrips.TruckID)=[forms]![frmTrips]!
[TruckID]));

I also note that this code step has commented out
the "As Date" portion of
the step; that makes dtLastTrip a Variant data type, not
a Date data type.
Is this what you wanted?
Dim dtLastTrip 'As Date

With the "As Date" in the code, I get a Run time
error '13': Type mismatch. I go to debug and the
"Dim dtLastTrip As Date" line is highlighted in yellow.
If I hold the cursor over the "dtLastTrip" it shows a
value of 12:00:00 AM.
Thanks,
Walter


--

Ken Snell
<MS ACCESS MVP>



message
I have the following code in a TruckID control to
validate the trip date for each truck. If I enter a
date
previous to the last date, the MsgBox appears with "The
last trip entered for this truck was on " and the OK
button. Can someone tell me why it is not showing the
date of the last trip? Also it is displaying the
MsgBox
even when a valid date is entered. Thanks to Dirk
Goldgar for the code. I have commented the "As Date"
because with it in place, I would get a "type mismatch"
error and the debug would show "dtLastTrip = 12:00:00
AM"

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

Thanks for your help,
Walter


.



.

.

 

[Ken Snell [MVP]]

Sorry... typo in my post. This is what you want to try in the Immediate
Window:

?[forms]![frmTrips]![TruckID]


Also, when you typed
??

as what was the result of the DLookup in the Immediate Window, I assume you
mean that nothing was shown; it appeared to be an empty line?

--

Ken Snell
<MS ACCESS MVP>

OK. After my last post I was able to display help(could
not display it before) and found out how to use
breakpoints.

-----Original Message-----
Put a breakpoint on the code step with the MsgBox line. When the code breaks
at that point, after you begin adding a new record, put the cursor over the
dtLastTrip variable. What is the value that is shown?

In trying to add a new record with a valid date I still
get the "type Mismatch" error. The dtLastTrip variable
shows 12:00:00 AM

Then, in the Immediate Window, type
?DLookup("TripDate", "qryLastTripDate")

What value is returned? ??

Also, in the Immediate window, type
?)[forms]![frmTrips]![TruckID]

In trying to print this ?)[forms]![frmTrips]![TruckID] my
computer locked up and I had to download an update. I
have to restart so I thought I would send this much. I'll
post the rest after restart.
Thanks,
Walter

What value is returned?

What value do you think is in the TruckID control on the form frmTrips?
--

Ken Snell
<MS ACCESS MVP>

-----Original Message-----
The data type mismatch, when you declare dtLastTrip as a
Date type, is being
caused when the DLookup function finds no records and is
returning a Null
value; a Date variable cannot accept a value of Null, so
the code errors.
When you comment out the "As Date", then dtLastTrip is
declared as a Variant
type, which can accept a Null value. Then, when its
value is Null, nothing
displays in your message box because Null has no value,
and there is nothing
to display.

The problem is that your DLookup is returning a value of
Null in these
cases. I assume that is because the query will return a
record * only if *
there is a child record in the tblTripDetails table for
the tblTrips.TruckID
value that is in the TruckID control that is on your
frmTrips form. If
there is no child record, there is no record returned by
the query, even if
you have a record in tblTrips for that TruckID.

All trips in the db have child records in
tblTripDetails.

Perhaps you need to modify your query:

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips LEFT JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
WHERE tblTrips.TruckID=[forms]![frmTrips]![TruckID]
GROUP BY tblTrips.TruckID;

The above query will return a record if there is a
record in tblTrips for
that TruckID * even if * you don't have a child record
in the tblTripDetails
table. It also will run a bit faster because I've
changed the HAVING filter
to a WHERE filter; WHERE filter is performed before the
query groups the
results (thus, nondesired records are filtered out
before grouping), whereas
HAVING filters after the grouping is done. Grouping
takes a lot of
resources, so it's better to filter beforer grouping if
you can do it.
--
I pasted your SQL in a blank query window, renamed the
old query and saved the new one as qryLastTripDate. I
then tried to add a new record using a valid date and got
the "Invalid date error" MsgBox with only the first line
of text displayed. I understand it not displaying a null
value but it is also not displaying the second line of
text; "Please enter a date greater than or equal to this
date."
Walter

Ken Snell
<MS ACCESS MVP>


message

-----Original Message-----
Your DLookup code step is not filtering the records in
any way. Thus, the
code step will return the "first" record that the
function finds in
qryLastTripDate. And if the query returns no records,
dtLastTrip will be a
Null value (see my comment below about how
you're "dimming" the variable).
And a Null will show as a blank entry in your
MsgBox.What is the SQL
statement of qryLastTripDate?

If I run the query by itself, providing a truck #, it
returns the correct date. Here is the SQL:

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips INNER JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
GROUP BY tblTrips.TruckID
HAVING (((tblTrips.TruckID)=[forms]![frmTrips]!
[TruckID]));

I also note that this code step has commented out
the "As Date" portion of
the step; that makes dtLastTrip a Variant data type,
not
a Date data type.
Is this what you wanted?
Dim dtLastTrip 'As Date

With the "As Date" in the code, I get a Run time
error '13': Type mismatch. I go to debug and the
"Dim dtLastTrip As Date" line is highlighted in yellow.
If I hold the cursor over the "dtLastTrip" it shows a
value of 12:00:00 AM.
Thanks,
Walter


--

Ken Snell
<MS ACCESS MVP>



message
I have the following code in a TruckID control to
validate the trip date for each truck. If I enter a
date
previous to the last date, the MsgBox appears
with "The
last trip entered for this truck was on " and the OK
button. Can someone tell me why it is not showing
the
date of the last trip? Also it is displaying the
MsgBox
even when a valid date is entered. Thanks to Dirk
Goldgar for the code. I have commented the "As Date"
because with it in place, I would get a "type
mismatch"
error and the debug would show "dtLastTrip = 12:00:00
AM"

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

Thanks for your help,
Walter


.



.

.

 

[Walter]

Continuation of my last post.

-----Original Message-----
Put a breakpoint on the code step with the MsgBox line. When the code breaks
at that point, after you begin adding a new record, put the cursor over the
dtLastTrip variable. What is the value that is shown?

Then, in the Immediate Window, type
?DLookup("TripDate", "qryLastTripDate")

What value is returned?

Also, in the Immediate window, type
?)[forms]![frmTrips]![TruckID]

I pasted this into the immediate window and got a Compile
Error: Expected: Expression.

What value is returned?

What value do you think is in the TruckID control on the

form frmTrips?

The value should be 209.

I am wondering if my db is corrupt or maybe I have a
memory problem. Sometimes, if I have several things open
and try to go to form view from design view after editing
code, Access will close. Also I have code to show or
hide some controls on my subform based on the value of
another control that was working fine but now is not
working and I haven't changed anything that would affect
this as far as I know.
Thanks so much for your help.
Walter

--

Ken Snell
<MS ACCESS MVP>

-----Original Message-----
The data type mismatch, when you declare dtLastTrip as

a
Date type, is being

caused when the DLookup function finds no records and

is
returning a Null

value; a Date variable cannot accept a value of Null,

so
the code errors.

When you comment out the "As Date", then dtLastTrip is declared as a Variant
type, which can accept a Null value. Then, when its value is Null, nothing
displays in your message box because Null has no value, and there is nothing
to display.

The problem is that your DLookup is returning a value

of
Null in these

cases. I assume that is because the query will return a record * only if *
there is a child record in the tblTripDetails table for the tblTrips.TruckID
value that is in the TruckID control that is on your frmTrips form. If
there is no child record, there is no record returned

by
the query, even if

you have a record in tblTrips for that TruckID.

All trips in the db have child records in
tblTripDetails.

Perhaps you need to modify your query:

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips LEFT JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
WHERE tblTrips.TruckID=[forms]![frmTrips]![TruckID]
GROUP BY tblTrips.TruckID;

The above query will return a record if there is a record in tblTrips for
that TruckID * even if * you don't have a child record in the tblTripDetails
table. It also will run a bit faster because I've changed the HAVING filter
to a WHERE filter; WHERE filter is performed before the query groups the
results (thus, nondesired records are filtered out before grouping), whereas
HAVING filters after the grouping is done. Grouping takes a lot of
resources, so it's better to filter beforer grouping if you can do it.
--

I pasted your SQL in a blank query window, renamed the
old query and saved the new one as qryLastTripDate. I
then tried to add a new record using a valid date and got
the "Invalid date error" MsgBox with only the first line
of text displayed. I understand it not displaying a null
value but it is also not displaying the second line of
text; "Please enter a date greater than or equal to this
date."
Walter

Ken Snell
<MS ACCESS MVP>



-----Original Message-----
Your DLookup code step is not filtering the records in
any way. Thus, the
code step will return the "first" record that the
function finds in
qryLastTripDate. And if the query returns no records,
dtLastTrip will be a
Null value (see my comment below about how
you're "dimming" the variable).
And a Null will show as a blank entry in your
MsgBox.What is the SQL
statement of qryLastTripDate?

If I run the query by itself, providing a truck #, it
returns the correct date. Here is the SQL:

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips INNER JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
GROUP BY tblTrips.TruckID
HAVING (((tblTrips.TruckID)=[forms]![frmTrips]!
[TruckID]));

I also note that this code step has commented out
the "As Date" portion of
the step; that makes dtLastTrip a Variant data type, not
a Date data type.
Is this what you wanted?
Dim dtLastTrip 'As Date

With the "As Date" in the code, I get a Run time
error '13': Type mismatch. I go to debug and the
"Dim dtLastTrip As Date" line is highlighted in yellow.
If I hold the cursor over the "dtLastTrip" it shows a
value of 12:00:00 AM.
Thanks,
Walter


--

Ken Snell
<MS ACCESS MVP>



message
I have the following code in a TruckID control to
validate the trip date for each truck. If I enter a
date
previous to the last date, the MsgBox appears with "The
last trip entered for this truck was on " and the OK
button. Can someone tell me why it is not showing the
date of the last trip? Also it is displaying the
MsgBox
even when a valid date is entered. Thanks to Dirk
Goldgar for the code. I have commented the "As Date"
because with it in place, I would get a "type mismatch"
error and the debug would show "dtLastTrip = 12:00:00
AM"

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

Thanks for your help,
Walter


.



.

.

 

[Walter]

-----Original Message-----
Sorry... typo in my post. This is what you want to try in the Immediate
Window:

?[forms]![frmTrips]![TruckID]

I deleted the ) and got Run-time Error: 2186
This property isn't available in design view.

Also, when you typed
??

as what was the result of the DLookup in the Immediate Window, I assume you
mean that nothing was shown; it appeared to be an empty

line?

The ?? was the literal response.

--

Ken Snell
<MS ACCESS MVP>

OK. After my last post I was able to display help(could
not display it before) and found out how to use
breakpoints.

-----Original Message-----
Put a breakpoint on the code step with the MsgBox line. When the code breaks
at that point, after you begin adding a new record, put the cursor over the
dtLastTrip variable. What is the value that is shown?

In trying to add a new record with a valid date I still
get the "type Mismatch" error. The dtLastTrip variable
shows 12:00:00 AM

Then, in the Immediate Window, type
?DLookup("TripDate", "qryLastTripDate")

What value is returned? ??

Also, in the Immediate window, type
?)[forms]![frmTrips]![TruckID]

In trying to print this ?)[forms]![frmTrips]![TruckID] my
computer locked up and I had to download an update. I
have to restart so I thought I would send this much. I'll
post the rest after restart.
Thanks,
Walter

What value is returned?

What value do you think is in the TruckID control on

the
form frmTrips?

--

Ken Snell
<MS ACCESS MVP>




-----Original Message-----
The data type mismatch, when you declare dtLastTrip

as
a

Date type, is being
caused when the DLookup function finds no records and is
returning a Null
value; a Date variable cannot accept a value of Null, so
the code errors.
When you comment out the "As Date", then dtLastTrip is
declared as a Variant
type, which can accept a Null value. Then, when its
value is Null, nothing
displays in your message box because Null has no value,
and there is nothing
to display.

The problem is that your DLookup is returning a value of
Null in these
cases. I assume that is because the query will return a
record * only if *
there is a child record in the tblTripDetails table for
the tblTrips.TruckID
value that is in the TruckID control that is on your
frmTrips form. If
there is no child record, there is no record returned by
the query, even if
you have a record in tblTrips for that TruckID.

All trips in the db have child records in
tblTripDetails.

Perhaps you need to modify your query:

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips LEFT JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
WHERE tblTrips.TruckID=[forms]![frmTrips]![TruckID]
GROUP BY tblTrips.TruckID;

The above query will return a record if there is a
record in tblTrips for
that TruckID * even if * you don't have a child record
in the tblTripDetails
table. It also will run a bit faster because I've
changed the HAVING filter
to a WHERE filter; WHERE filter is performed before the
query groups the
results (thus, nondesired records are filtered out
before grouping), whereas
HAVING filters after the grouping is done. Grouping
takes a lot of
resources, so it's better to filter beforer grouping if
you can do it.
--
I pasted your SQL in a blank query window, renamed the
old query and saved the new one as qryLastTripDate. I
then tried to add a new record using a valid date and got
the "Invalid date error" MsgBox with only the first line
of text displayed. I understand it not displaying a null
value but it is also not displaying the second line of
text; "Please enter a date greater than or equal to this
date."
Walter

Ken Snell
<MS ACCESS MVP>


message

-----Original Message-----
Your DLookup code step is not filtering the records in
any way. Thus, the
code step will return the "first" record that the
function finds in
qryLastTripDate. And if the query returns no records,
dtLastTrip will be a
Null value (see my comment below about how
you're "dimming" the variable).
And a Null will show as a blank entry in your
MsgBox.What is the SQL
statement of qryLastTripDate?

If I run the query by itself, providing a truck #, it
returns the correct date. Here is the SQL:

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips INNER JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
GROUP BY tblTrips.TruckID
HAVING (((tblTrips.TruckID)=[forms]![frmTrips]!
[TruckID]));

I also note that this code step has commented out
the "As Date" portion of
the step; that makes dtLastTrip a Variant data type,
not
a Date data type.
Is this what you wanted?
Dim dtLastTrip 'As Date

With the "As Date" in the code, I get a Run time
error '13': Type mismatch. I go to debug and the
"Dim dtLastTrip As Date" line is highlighted in yellow.
If I hold the cursor over the "dtLastTrip" it shows a
value of 12:00:00 AM.
Thanks,
Walter


--

Ken Snell
<MS ACCESS MVP>



"Walter" <[email protected]>

wrote
in

message
I have the following code in a TruckID control to
validate the trip date for each truck. If I

enter
a

date
previous to the last date, the MsgBox appears
with "The
last trip entered for this truck was on " and the OK
button. Can someone tell me why it is not showing
the
date of the last trip? Also it is displaying the
MsgBox
even when a valid date is entered. Thanks to Dirk
Goldgar for the code. I have commented the "As Date"
because with it in place, I would get a "type
mismatch"
error and the debug would show "dtLastTrip = 12:00:00
AM"

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

Thanks for your help,
Walter


.



.



.

.

 

[Ken Snell [MVP]]

What you're saying are the results either indicate that you may indeed have
a corrupt database, or that your setup is completely different from what
you're thinking it is... and I don't believe the latter situation is true.

Compact and repair the database and then see if things are better. Post back
and we'll go from there.

--

Ken Snell
<MS ACCESS MVP>

Continuation of my last post.

-----Original Message-----
Put a breakpoint on the code step with the MsgBox line. When the code breaks
at that point, after you begin adding a new record, put the cursor over the
dtLastTrip variable. What is the value that is shown?

Then, in the Immediate Window, type
?DLookup("TripDate", "qryLastTripDate")

What value is returned?

Also, in the Immediate window, type
?)[forms]![frmTrips]![TruckID]

I pasted this into the immediate window and got a Compile
Error: Expected: Expression.

What value is returned?

What value do you think is in the TruckID control on the

form frmTrips?

The value should be 209.

I am wondering if my db is corrupt or maybe I have a
memory problem. Sometimes, if I have several things open
and try to go to form view from design view after editing
code, Access will close. Also I have code to show or
hide some controls on my subform based on the value of
another control that was working fine but now is not
working and I haven't changed anything that would affect
this as far as I know.
Thanks so much for your help.
Walter

--

Ken Snell
<MS ACCESS MVP>

-----Original Message-----
The data type mismatch, when you declare dtLastTrip as a
Date type, is being
caused when the DLookup function finds no records and is
returning a Null
value; a Date variable cannot accept a value of Null, so
the code errors.
When you comment out the "As Date", then dtLastTrip is
declared as a Variant
type, which can accept a Null value. Then, when its
value is Null, nothing
displays in your message box because Null has no value,
and there is nothing
to display.

The problem is that your DLookup is returning a value of
Null in these
cases. I assume that is because the query will return a
record * only if *
there is a child record in the tblTripDetails table for
the tblTrips.TruckID
value that is in the TruckID control that is on your
frmTrips form. If
there is no child record, there is no record returned by
the query, even if
you have a record in tblTrips for that TruckID.

All trips in the db have child records in
tblTripDetails.

Perhaps you need to modify your query:

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips LEFT JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
WHERE tblTrips.TruckID=[forms]![frmTrips]![TruckID]
GROUP BY tblTrips.TruckID;

The above query will return a record if there is a
record in tblTrips for
that TruckID * even if * you don't have a child record
in the tblTripDetails
table. It also will run a bit faster because I've
changed the HAVING filter
to a WHERE filter; WHERE filter is performed before the
query groups the
results (thus, nondesired records are filtered out
before grouping), whereas
HAVING filters after the grouping is done. Grouping
takes a lot of
resources, so it's better to filter beforer grouping if
you can do it.
--
I pasted your SQL in a blank query window, renamed the
old query and saved the new one as qryLastTripDate. I
then tried to add a new record using a valid date and got
the "Invalid date error" MsgBox with only the first line
of text displayed. I understand it not displaying a null
value but it is also not displaying the second line of
text; "Please enter a date greater than or equal to this
date."
Walter

Ken Snell
<MS ACCESS MVP>


message

-----Original Message-----
Your DLookup code step is not filtering the records in
any way. Thus, the
code step will return the "first" record that the
function finds in
qryLastTripDate. And if the query returns no records,
dtLastTrip will be a
Null value (see my comment below about how
you're "dimming" the variable).
And a Null will show as a blank entry in your
MsgBox.What is the SQL
statement of qryLastTripDate?

If I run the query by itself, providing a truck #, it
returns the correct date. Here is the SQL:

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips INNER JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
GROUP BY tblTrips.TruckID
HAVING (((tblTrips.TruckID)=[forms]![frmTrips]!
[TruckID]));

I also note that this code step has commented out
the "As Date" portion of
the step; that makes dtLastTrip a Variant data type,
not
a Date data type.
Is this what you wanted?
Dim dtLastTrip 'As Date

With the "As Date" in the code, I get a Run time
error '13': Type mismatch. I go to debug and the
"Dim dtLastTrip As Date" line is highlighted in yellow.
If I hold the cursor over the "dtLastTrip" it shows a
value of 12:00:00 AM.
Thanks,
Walter


--

Ken Snell
<MS ACCESS MVP>



message
I have the following code in a TruckID control to
validate the trip date for each truck. If I enter a
date
previous to the last date, the MsgBox appears
with "The
last trip entered for this truck was on " and the OK
button. Can someone tell me why it is not showing
the
date of the last trip? Also it is displaying the
MsgBox
even when a valid date is entered. Thanks to Dirk
Goldgar for the code. I have commented the "As Date"
because with it in place, I would get a "type
mismatch"
error and the debug would show "dtLastTrip = 12:00:00
AM"

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

Thanks for your help,
Walter


.



.

.

 

[Dirk Goldgar]

Continuation of my last post.

-----Original Message-----
Put a breakpoint on the code step with the MsgBox line. When the
code breaks at that point, after you begin adding a new record, put
the cursor over the dtLastTrip variable. What is the value that is
shown?

Then, in the Immediate Window, type
?DLookup("TripDate", "qryLastTripDate")

What value is returned?

Also, in the Immediate window, type
?)[forms]![frmTrips]![TruckID]

I pasted this into the immediate window and got a Compile
Error: Expected: Expression.

What value is returned?

What value do you think is in the TruckID control on the form
frmTrips?

The value should be 209.

I am wondering if my db is corrupt or maybe I have a
memory problem. Sometimes, if I have several things open
and try to go to form view from design view after editing
code, Access will close. Also I have code to show or
hide some controls on my subform based on the value of
another control that was working fine but now is not
working and I haven't changed anything that would affect
this as far as I know.
Thanks so much for your help.

[...]
(posted as the query)

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips LEFT JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
WHERE tblTrips.TruckID=[forms]![frmTrips]![TruckID]
GROUP BY tblTrips.TruckID;

Walter, Ken -

If the above SQL is the body of qryLastTripDate, then the query
*contains no field named TripDate*! So the DLookup expression,

DLookup("TripDate", "qryLastTripDate")

must fail. I suggest changing the SQL to

SELECT
tblTrips.TruckID,
Max(tblTrips.TripDate) AS LastTripDate
FROM
tblTrips
LEFT JOIN
tblTripDetails
ON tblTrips.TripID = tblTripDetails.TripID
WHERE tblTrips.TruckID=[forms]![frmTrips]![TruckID]
GROUP BY tblTrips.TruckID;

.... and then using this statement in your code:

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

Although, now that I look at it, it appears that TripDate is stored in
tblTrips along with TruckID, so I don't see (now) why you need
qryLastTripDate at all. Why can't you just use

dtLastTrip = _
DMax("TripDate", "tblTrips", "TruckID=" & Me.TruckID)

or, if this code isn't running on frmTrips, this:

dtLastTrip = _
DMax("TripDate", "tblTrips", _
"TruckID=" & Forms!frmTrips!TruckID)

 

[Ken Snell [MVP]]

Thanks for the extra set of eyes, Dirk! < g > You sometimes get so focused
on one aspect that other issues escape notice.
--

Ken Snell
<MS ACCESS MVP>

Continuation of my last post.

-----Original Message-----
Put a breakpoint on the code step with the MsgBox line. When the
code breaks at that point, after you begin adding a new record, put
the cursor over the dtLastTrip variable. What is the value that is
shown?

Then, in the Immediate Window, type
?DLookup("TripDate", "qryLastTripDate")

What value is returned?

Also, in the Immediate window, type
?)[forms]![frmTrips]![TruckID]

I pasted this into the immediate window and got a Compile
Error: Expected: Expression.

What value is returned?

What value do you think is in the TruckID control on the form
frmTrips?

The value should be 209.

I am wondering if my db is corrupt or maybe I have a
memory problem. Sometimes, if I have several things open
and try to go to form view from design view after editing
code, Access will close. Also I have code to show or
hide some controls on my subform based on the value of
another control that was working fine but now is not
working and I haven't changed anything that would affect
this as far as I know.
Thanks so much for your help.

[...]
(posted as the query)

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips LEFT JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
WHERE tblTrips.TruckID=[forms]![frmTrips]![TruckID]
GROUP BY tblTrips.TruckID;

Walter, Ken -

If the above SQL is the body of qryLastTripDate, then the query
*contains no field named TripDate*! So the DLookup expression,

DLookup("TripDate", "qryLastTripDate")

must fail. I suggest changing the SQL to

SELECT
tblTrips.TruckID,
Max(tblTrips.TripDate) AS LastTripDate
FROM
tblTrips
LEFT JOIN
tblTripDetails
ON tblTrips.TripID = tblTripDetails.TripID
WHERE tblTrips.TruckID=[forms]![frmTrips]![TruckID]
GROUP BY tblTrips.TruckID;

... and then using this statement in your code:

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

Although, now that I look at it, it appears that TripDate is stored in
tblTrips along with TruckID, so I don't see (now) why you need
qryLastTripDate at all. Why can't you just use

dtLastTrip = _
DMax("TripDate", "tblTrips", "TruckID=" & Me.TruckID)

or, if this code isn't running on frmTrips, this:

dtLastTrip = _
DMax("TripDate", "tblTrips", _
"TruckID=" & Forms!frmTrips!TruckID)

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

(please reply to the newsgroup)

 

[Walter]

Continuation of my last post.

-----Original Message-----
Put a breakpoint on the code step with the MsgBox line. When the
code breaks at that point, after you begin adding a new record, put
the cursor over the dtLastTrip variable. What is the value that is
shown?

Then, in the Immediate Window, type
?DLookup("TripDate", "qryLastTripDate")

What value is returned?

Also, in the Immediate window, type
?)[forms]![frmTrips]![TruckID]

I pasted this into the immediate window and got a Compile
Error: Expected: Expression.

What value is returned?

What value do you think is in the TruckID control on the form
frmTrips?

The value should be 209.

I am wondering if my db is corrupt or maybe I have a
memory problem. Sometimes, if I have several things open
and try to go to form view from design view after editing
code, Access will close. Also I have code to show or
hide some controls on my subform based on the value of
another control that was working fine but now is not
working and I haven't changed anything that would affect
this as far as I know.
Thanks so much for your help.

[...]
(posted as the query)

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips LEFT JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
WHERE tblTrips.TruckID=[forms]![frmTrips]![TruckID]
GROUP BY tblTrips.TruckID;

Walter, Ken -

If the above SQL is the body of qryLastTripDate, then the query
*contains no field named TripDate*! So the DLookup expression,

DLookup("TripDate", "qryLastTripDate")

must fail. I suggest changing the SQL to

SELECT
tblTrips.TruckID,
Max(tblTrips.TripDate) AS LastTripDate
FROM
tblTrips
LEFT JOIN
tblTripDetails
ON tblTrips.TripID = tblTripDetails.TripID
WHERE tblTrips.TruckID=[forms]![frmTrips]![TruckID]
GROUP BY tblTrips.TruckID;

.... and then using this statement in your code:

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

Although, now that I look at it, it appears that TripDate is stored in
tblTrips along with TruckID, so I don't see (now) why you need
qryLastTripDate at all. Why can't you just use

dtLastTrip = _
DMax("TripDate", "tblTrips", "TruckID=" & Me.TruckID)

Hi Dirk,
Thanks for all your help a few days ago(SQL Compile Error). You are correct
that TripDate is stored in tblTrips. I created the query to select the
correct record for a specific truck. I know very little about Access and
programming and so I try to use what I know how to. I sincerely appreciate
the time all of you spend helping those of us who get in over our heads so to
speak. I am assuming that DMax("TripDate", "tblTrips", "TruckID=" &
Me.TruckID) would replace the DLookup("TripDate", "qryLastTripDate") line
and the rest of the code remain the same. I am going to try this and see
what happens. I'll let you know.

 

[Walter]

Being a novice, I don't want to mess up anything so if you could maybe give
me some pointers as how to go about this. I've read Dirk's reply and am
going to try that but as I said I still have some other things that don't
seem right.

What you're saying are the results either indicate that you may indeed have
a corrupt database, or that your setup is completely different from what
you're thinking it is... and I don't believe the latter situation is true.

Compact and repair the database and then see if things are better. Post back
and we'll go from there.

--

Ken Snell
<MS ACCESS MVP>

Continuation of my last post.

-----Original Message-----
Put a breakpoint on the code step with the MsgBox line. When the code breaks
at that point, after you begin adding a new record, put the cursor over the
dtLastTrip variable. What is the value that is shown?

Then, in the Immediate Window, type
?DLookup("TripDate", "qryLastTripDate")

What value is returned?

Also, in the Immediate window, type
?)[forms]![frmTrips]![TruckID]

I pasted this into the immediate window and got a Compile
Error: Expected: Expression.

What value is returned?

What value do you think is in the TruckID control on the

form frmTrips?

The value should be 209.

I am wondering if my db is corrupt or maybe I have a
memory problem. Sometimes, if I have several things open
and try to go to form view from design view after editing
code, Access will close. Also I have code to show or
hide some controls on my subform based on the value of
another control that was working fine but now is not
working and I haven't changed anything that would affect
this as far as I know.
Thanks so much for your help.
Walter

--

Ken Snell
<MS ACCESS MVP>




-----Original Message-----
The data type mismatch, when you declare dtLastTrip as a
Date type, is being
caused when the DLookup function finds no records and is
returning a Null
value; a Date variable cannot accept a value of Null, so
the code errors.
When you comment out the "As Date", then dtLastTrip is
declared as a Variant
type, which can accept a Null value. Then, when its
value is Null, nothing
displays in your message box because Null has no value,
and there is nothing
to display.

The problem is that your DLookup is returning a value of
Null in these
cases. I assume that is because the query will return a
record * only if *
there is a child record in the tblTripDetails table for
the tblTrips.TruckID
value that is in the TruckID control that is on your
frmTrips form. If
there is no child record, there is no record returned by
the query, even if
you have a record in tblTrips for that TruckID.

All trips in the db have child records in
tblTripDetails.

Perhaps you need to modify your query:

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips LEFT JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
WHERE tblTrips.TruckID=[forms]![frmTrips]![TruckID]
GROUP BY tblTrips.TruckID;

The above query will return a record if there is a
record in tblTrips for
that TruckID * even if * you don't have a child record
in the tblTripDetails
table. It also will run a bit faster because I've
changed the HAVING filter
to a WHERE filter; WHERE filter is performed before the
query groups the
results (thus, nondesired records are filtered out
before grouping), whereas
HAVING filters after the grouping is done. Grouping
takes a lot of
resources, so it's better to filter beforer grouping if
you can do it.
--
I pasted your SQL in a blank query window, renamed the
old query and saved the new one as qryLastTripDate. I
then tried to add a new record using a valid date and got
the "Invalid date error" MsgBox with only the first line
of text displayed. I understand it not displaying a null
value but it is also not displaying the second line of
text; "Please enter a date greater than or equal to this
date."
Walter

Ken Snell
<MS ACCESS MVP>


message

-----Original Message-----
Your DLookup code step is not filtering the records in
any way. Thus, the
code step will return the "first" record that the
function finds in
qryLastTripDate. And if the query returns no records,
dtLastTrip will be a
Null value (see my comment below about how
you're "dimming" the variable).
And a Null will show as a blank entry in your
MsgBox.What is the SQL
statement of qryLastTripDate?

If I run the query by itself, providing a truck #, it
returns the correct date. Here is the SQL:

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips INNER JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
GROUP BY tblTrips.TruckID
HAVING (((tblTrips.TruckID)=[forms]![frmTrips]!
[TruckID]));

I also note that this code step has commented out
the "As Date" portion of
the step; that makes dtLastTrip a Variant data type,
not
a Date data type.
Is this what you wanted?
Dim dtLastTrip 'As Date

With the "As Date" in the code, I get a Run time
error '13': Type mismatch. I go to debug and the
"Dim dtLastTrip As Date" line is highlighted in yellow.
If I hold the cursor over the "dtLastTrip" it shows a
value of 12:00:00 AM.
Thanks,
Walter


--

Ken Snell
<MS ACCESS MVP>



message
I have the following code in a TruckID control to
validate the trip date for each truck. If I enter a
date
previous to the last date, the MsgBox appears
with "The
last trip entered for this truck was on " and the OK
button. Can someone tell me why it is not showing
the
date of the last trip? Also it is displaying the
MsgBox
even when a valid date is entered. Thanks to Dirk
Goldgar for the code. I have commented the "As Date"
because with it in place, I would get a "type
mismatch"
error and the debug would show "dtLastTrip = 12:00:00
AM"

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

Thanks for your help,
Walter


.



.



.

 

[Walter]

OK. I pasted your code (DMax("TripDate", "tblTrips", "TruckID=" &
Me.TruckID)) in place of the DLookup statement. I entered a valid date,
selected a truck and got the error: Run-time error '3464': data type
mismatch in criteria expression. I went to debug to check the variables.
They are: dtLastTrip = 12:00:00 AM, Me.TripDate = 11/20/2004 which is
correct, and Me.TruckID = 209 which is also correct. Why is dtLastTrip
showing a time instead of a date?

Continuation of my last post.

-----Original Message-----
Put a breakpoint on the code step with the MsgBox line. When the
code breaks at that point, after you begin adding a new record, put
the cursor over the dtLastTrip variable. What is the value that is
shown?

Then, in the Immediate Window, type
?DLookup("TripDate", "qryLastTripDate")

What value is returned?

Also, in the Immediate window, type
?)[forms]![frmTrips]![TruckID]

I pasted this into the immediate window and got a Compile
Error: Expected: Expression.

What value is returned?

What value do you think is in the TruckID control on the form
frmTrips?

The value should be 209.

I am wondering if my db is corrupt or maybe I have a
memory problem. Sometimes, if I have several things open
and try to go to form view from design view after editing
code, Access will close. Also I have code to show or
hide some controls on my subform based on the value of
another control that was working fine but now is not
working and I haven't changed anything that would affect
this as far as I know.
Thanks so much for your help.

[...]
(posted as the query)

SELECT tblTrips.TruckID, Max(tblTrips.TripDate) AS
MaxOfTripDate
FROM tblTrips LEFT JOIN tblTripDetails ON
tblTrips.TripID = tblTripDetails.TripID
WHERE tblTrips.TruckID=[forms]![frmTrips]![TruckID]
GROUP BY tblTrips.TruckID;

Walter, Ken -

If the above SQL is the body of qryLastTripDate, then the query
*contains no field named TripDate*! So the DLookup expression,

DLookup("TripDate", "qryLastTripDate")

must fail. I suggest changing the SQL to

SELECT
tblTrips.TruckID,
Max(tblTrips.TripDate) AS LastTripDate
FROM
tblTrips
LEFT JOIN
tblTripDetails
ON tblTrips.TripID = tblTripDetails.TripID
WHERE tblTrips.TruckID=[forms]![frmTrips]![TruckID]
GROUP BY tblTrips.TruckID;

.... and then using this statement in your code:

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

Although, now that I look at it, it appears that TripDate is stored in
tblTrips along with TruckID, so I don't see (now) why you need
qryLastTripDate at all. Why can't you just use

dtLastTrip = _
DMax("TripDate", "tblTrips", "TruckID=" & Me.TruckID)

or, if this code isn't running on frmTrips, this:

dtLastTrip = _
DMax("TripDate", "tblTrips", _
"TruckID=" & Forms!frmTrips!TruckID)

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

(please reply to the newsgroup)

 

[Dirk Goldgar]

OK. I pasted your code (DMax("TripDate", "tblTrips", "TruckID=" &
Me.TruckID)) in place of the DLookup statement. I entered a valid
date, selected a truck and got the error: Run-time error '3464':
data type mismatch in criteria expression. I went to debug to check
the variables. They are: dtLastTrip = 12:00:00 AM, Me.TripDate =
11/20/2004 which is correct, and Me.TruckID = 209 which is also
correct. Why is dtLastTrip showing a time instead of a date?

A date value of #12:00:00 AM#, with no actual date shown, represents a
date value of 0, which in long form is Saturday, December 30, 1899,
12:00:00 AM. I guess that dtLastTrip is showing this value because it
is never receiving the value from the DMax() function expression,
because that expression is raising the error.

I don't remember from our previous exchanges -- is TruckID a text field?
If so, the DMax expression would have to be like this:

DMax("TripDate", "tblTrips", "TruckID='" & Me.TruckID & "'")

Notice the single-quotes that now surround the value concatenated into
the criterion from Me.TruckID. (Double-quotes could have been used, but
single-quotes are easier if no TruckID will ever include a single-quote
character.)

 

[Walter]

You are right. TruckID is a text field so I changed the code and the MsgBox
comes up as it should. BTW should the TruckID be a number field if truck
numbers will not contain letters or does it really matter? Clicking OK in
the MsgBox brings up another box saying "Value in the field violates the
validation rule". OK here leaves you in the TruckID field and can't exit. I
tried Me.TripDate.Setfocus after Cancel = True and got Error '2108': You must
save the field before you execute the SetFocus method. I added
Me.TruckID.Undo before Me.TripDate.SetFocus and still get the same error.
How do I get the focus back to TripDate to change it? Here is my present code:
Private Sub TruckID_BeforeUpdate(Cancel As Integer)
Dim dtLastTrip As Date

dtLastTrip = DMax("TripDate", "tblTrips", "TruckID='" & Me.TruckID & "'")


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
Me.TruckID.Undo
Me.TripDate.SetFocus
End If

End Sub
Thanks,
Walter

 

[Dirk Goldgar]

You are right. TruckID is a text field so I changed the code and the
MsgBox comes up as it should. BTW should the TruckID be a number
field if truck numbers will not contain letters or does it really
matter? Clicking OK in the MsgBox brings up another box saying
"Value in the field violates the validation rule". OK here leaves
you in the TruckID field and can't exit. I tried Me.TripDate.Setfocus
after Cancel = True and got Error '2108': You must save the field
before you execute the SetFocus method. I added Me.TruckID.Undo
before Me.TripDate.SetFocus and still get the same error.
How do I get the focus back to TripDate to change it? Here is my
present code: Private Sub TruckID_BeforeUpdate(Cancel As Integer)
Dim dtLastTrip As Date

dtLastTrip = DMax("TripDate", "tblTrips", "TruckID='" &
Me.TruckID & "'")


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
Me.TruckID.Undo
Me.TripDate.SetFocus
End If

End Sub
Thanks,
Walter

I don't know where a validation rule comes into it. Do you have a
validation rule set -- either in the table design or in one of the
controls on the form -- that could be raising the validation error?

It seems to me that your logic is a little confused in the procedure you
posted, because you are using the BeforeUpdate event of TruckID to
validate TripDate. I haven't really looked at this logic before, being
wrapped up in the question about the DLookup/DMax expression, but now
that I think about it, I see an inconsistency. Normally you would use
the BeforeUpdate event of TruckID to validate TruckID, and cancel the
event if the value of TruckID is invalid. Cancelling the event keeps
the focus in the control (that's why your TripDate.SetFocus isn't
working). Similarly, you would use the BeforeUpdate event of TripDate
to validate the value entered for TripDate, and cancel the event if the
date entered is invalid, forcing the user to stay in the control and
enter a different date.

What is the actual user-interface logic here? In what order do you
expect the user to fill in the fields on this form? If TripDate is
filled in first and TruckID second, and the two aren't a valid
combination (because the TripDate < dtLastTrip), does that mean that
TripDate is wrong or that TruckID is wrong? Which one should be
corrected? The same question may be asked if the fields are entered in
the reverse order. Does the order in which they are entered make a
difference as to which field is considered to be in error?

Because both fields are involved in the TripDate validation, it may be
best to defer the validation until the form's BeforeUpdate event.
That's where I usually put logic that tests for inter-field consistency.
Then it doesn't matter in what order fields are filled in; it's just
the state of the record as it is about to be saved that matters.

I could revise your current code so as to solve your immediate
problem -- all you have to do is not undo TruckID, and not cancel the
event -- but I'm not convinced that's the best solution over all. I'd
recommend that you first think through the user-interface logic and all
the possible scenarios regarding this validation. When you've done that
and can state exactly what you want to have happen in all circumstances,
then the best solution will be apparent.