Just remembered I need to change that function, because I have written it
to
use my own error handler.
Here is the changed code:
Paste all the code below into a new module
'start of SQLDate code ------------------------------------
Option Compare Database
Option Explicit
Public Function SQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used natively
by
JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time
component,
' or a date/time format if it does.
'Author: Allen Browne. (e-mail address removed), June 2006.
On Error GoTo Err_Handler
If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
Exit_Handler:
Exit Function
Err_Handler:
MsgBox Err.Number & ", " & Err.Description
Resume Exit_Handler
End Function
'endof SQLDate code ------------------------------------
Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
Jeanette Cunningham said:
Oops,
you also need a function to get SQLDate
Paste all the code below into a new module
'start of SQLDate code ------------------------------------
Option Compare Database
Option Explicit
Public Function SQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used natively
by
JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time
component,
' or a date/time format if it does.
'Author: Allen Browne. (e-mail address removed), June 2006.
On Error GoTo Err_Handler
pstrProc = "SQLDate"
If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
Exit_Handler:
Exit Function
Err_Handler:
Call fnFormErrHandler(pstrProc, pstrMdl, Err)
Resume Exit_Handler
End Function
'endof SQLDate code ------------------------------------
--
Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
Mmm ..
base the data sheet on the normal query, not the totals query.
The totals query is used to get the previous odo.
Here is code you can put into the Load event for your subform.
Private Sub Form_Load()
dim dtePrev As Date
Dim lngPrevOdo as Long
Dim strCriteria as String
dtePrev = Nz(DLookup("[NameOfTotalsDateField]",
"NameOfTotalsQuery"),0)
Debug.Print dtePrev
strCriteria = "[FillupDate] = " & SQLDate(detPrev0 & ""
Debug.Print strCriteria
lngPrevOdo = DLookup("[NameOfOdoField]", "[NameOfMileageTable]",
strCriteria)
Debug.Print lngPrevOdo
End Sub
Use the value for lngPrevOdo in your calculation for mileage.
If you wish to show the previous odo on the form, you can make an
unbound
textbox and put its control source
=Nz(lngPrevOdo,0)
Replace the obvious with your object and control names.
Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
:
You will need to change the query that the form is based on - it is
probably
looking for the value for previous odo and can't find it.
Get the datasheet to work properly, then we can help with the
dlookup.
Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
:
Hi Marie,
I suggest that you make a small change to the Mileage table.
Remove the field called PreviousODO - you can always find the
value
for
ProviousODO without storing it twice in that table.
It is stored twice because it is first entered as CurrentODO,
then
in the
next record it is repeated again but this time called
PreviousODO.
When you need to find the PreviousODO to use for your
calculation,
you
can
look it up using DLookup and the most recent date.
Create a query based on the mileage table, using the primary key
field,
the
CurrentODO and the FillupDate.
Change the query to a totals query and in the totals row under
FillupDate,
put Max.
This will give you the previous ODO for the most recent date.
Post back if you need help with the dlookup.
Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
Hello,
I am working on a project that keeps track of our fleet of
buses.
One
thing
we need to keep track of is each time a bus fuels up, we need
the
previous
odometer reading, the current odometer reading, and the amount
of
gallons
used to full the tank. I then have a calculated field on the
form
only
to
show the amount of miles driven between each fuel up. Right
now, I
have
a
Vehicle table, and a Mileage table. It is one to many. I have a
form
for
the
vehicles, and a subform for the mileage. On the subform, these
are
the
fields: FillupDate, PreviousODO, CurrentODO, and Gallons. I
would
like
to
have the Current ODO populate the next record for the same
vehicle
in
the
PreviousODO field. I have looked for the answer on this forum,
but
so
far
nothing has worked for me. The VehID field is text, based on
the
VIN
number.
I appreciate any help that you can give me.
Thank you for your reply, Jeanette. When I did as you suggested,
the
datasheet did not show beneath the titles.. Also, where do I put
the
dlookup? Thanks.
I deleted PreviousODO from the Mileage table and created a new query.
I
then
based the subform on the new query. If I disengage the totals button,
the
daasheet shows. As soon as I add totals, the datasheet disappears.
The
main
query is based on the Vehicles table.
