Show previous months reading on new months record

[Gabby Girl]

Good afternoon,

I have been trying to figure out for the last three days now, how I can show
the previous months fuel readings on the next months record. I don't know if
I've been looking at it too long or what but I've hit a brick wall in trying
to get this to work. My tables are:

tblFuelTracking
FuelId - PK
FuelDate
Location (10 different locations in all)

tblFuelDips
DipId - PK
FuelId - FK
DipMeterDate
EndingDipReading
EndingDipMeter

I've tried subqueries, DLookups and anything else I could think of, but I'm
really unclear how I can have my July 31st readings show up when I enter the
August 31st readings for the same location.

Could someone please give me some quidance on this before I commit myself?

Any help would be greatly appreciated.

Thanks kindly

 

[Ken Snell \(MVP\)]

I assume that you don't want to hard-code the July 31 date into your setup,
so we'll need to know a few more things.

Are you entering the August 31 date on the form? Is that when you want the
form to show the last month's reading? and do you want to show both the date
and fill value at that time -- and in separate textboxes or as a
concatenated string that tells you the info?

What is the form's RecordSource query?

 

[Gabby Girl]

Hi Ken,

Thank you for taking the time to read my post. You're correct that I don't
want hard code in the date and yes when the user enters the next months info
and enters 08/31/07, I'd like to see the last month's readings pop up in two
text boxes. I have a main form and 3 subforms. On the main form the user
enters the date (always the last day of the month) and the shed location.
Then they can either enter the monthly details on the first subform (ie: Unit
number and how many litres they took), then on the second subform (which also
has a subform) they enter the ending gas dip readings and the ending meter
readings for the month. The subform embedded in the Dip readings subform is
show the previous months values.

There are three queries involved:
Main form = qselFuelTracking
1st subform = qsubFuelTrackingDetails (linked by FuelId)
2nd subform = qsubFuelDips (linked by FuelId)
3rd subform = qsubFuelDipsPrior (thought I could link via DipMeterDate &
LMDate)

I was trying to use a fourth query (qsubFuelDipsPrior) to get the previous
readings from. I added a column & named it
LMDateateAdd("m",1,[DipMeterDate]) thinking that I could link LMDate to
DipMeterDate. Right now that gives me the correct last month date with the
exception of July. My DipMeterDate is 07/31/07 but the LMDate is 07/30/07???.
Now I'm totally confused. Am I even on the right track?

Thanks kindly

 

[Ken Snell \(MVP\)]

I'm not understanding why the third subform wouldn't just show all the
previous records and then you wouldn't need to separately show the previous
months, but...

Here are generic expressions that you might use as control source
expressions in textboxes (in same form or subform as where you enter the
current date item) to show the two desired data values:

For previousdate:
=DMax("DipMeterDate","NameOfTheTable","DipMeterDate<#" &
Format([NameOfTextBoxWhereYouEnterNewDate],"mm\/dd\/yyyy") & "#")


For previus fill reading:
=DLookup("EndingDipReading", "TableName","DipMeterDate=" &
DMax("DipMeterDate","NameOfTheTable","DipMeterDate<#" &
Format([NameOfTextBoxWhereYouEnterNewDate],"mm\/dd\/yyyy") & "#"))


--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Thank you for taking the time to read my post. You're correct that I
don't
want hard code in the date and yes when the user enters the next months
info
and enters 08/31/07, I'd like to see the last month's readings pop up in
two
text boxes. I have a main form and 3 subforms. On the main form the user
enters the date (always the last day of the month) and the shed location.
Then they can either enter the monthly details on the first subform (ie:
Unit
number and how many litres they took), then on the second subform (which
also
has a subform) they enter the ending gas dip readings and the ending meter
readings for the month. The subform embedded in the Dip readings subform
is
show the previous months values.

There are three queries involved:
Main form = qselFuelTracking
1st subform = qsubFuelTrackingDetails (linked by FuelId)
2nd subform = qsubFuelDips (linked by FuelId)
3rd subform = qsubFuelDipsPrior (thought I could link via DipMeterDate &
LMDate)

I was trying to use a fourth query (qsubFuelDipsPrior) to get the previous
readings from. I added a column & named it
LMDateateAdd("m",1,[DipMeterDate]) thinking that I could link LMDate to
DipMeterDate. Right now that gives me the correct last month date with the
exception of July. My DipMeterDate is 07/31/07 but the LMDate is
07/30/07???.
Now I'm totally confused. Am I even on the right track?

Thanks kindly

I assume that you don't want to hard-code the July 31 date into your
setup,
so we'll need to know a few more things.

Are you entering the August 31 date on the form? Is that when you want
the
form to show the last month's reading? and do you want to show both the
date
and fill value at that time -- and in separate textboxes or as a
concatenated string that tells you the info?

What is the form's RecordSource query?

 

[Gabby Girl]

Hi Ken,

Sorry for the delay. I'll try your suggestions in the morning and let you
know how it goes. I really do appreciate your help.

Thanks again.

I'm not understanding why the third subform wouldn't just show all the
previous records and then you wouldn't need to separately show the previous
months, but...

Here are generic expressions that you might use as control source
expressions in textboxes (in same form or subform as where you enter the
current date item) to show the two desired data values:

For previousdate:
=DMax("DipMeterDate","NameOfTheTable","DipMeterDate<#" &
Format([NameOfTextBoxWhereYouEnterNewDate],"mm\/dd\/yyyy") & "#")


For previus fill reading:
=DLookup("EndingDipReading", "TableName","DipMeterDate=" &
DMax("DipMeterDate","NameOfTheTable","DipMeterDate<#" &
Format([NameOfTextBoxWhereYouEnterNewDate],"mm\/dd\/yyyy") & "#"))


--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Thank you for taking the time to read my post. You're correct that I
don't
want hard code in the date and yes when the user enters the next months
info
and enters 08/31/07, I'd like to see the last month's readings pop up in
two
text boxes. I have a main form and 3 subforms. On the main form the user
enters the date (always the last day of the month) and the shed location.
Then they can either enter the monthly details on the first subform (ie:
Unit
number and how many litres they took), then on the second subform (which
also
has a subform) they enter the ending gas dip readings and the ending meter
readings for the month. The subform embedded in the Dip readings subform
is
show the previous months values.

There are three queries involved:
Main form = qselFuelTracking
1st subform = qsubFuelTrackingDetails (linked by FuelId)
2nd subform = qsubFuelDips (linked by FuelId)
3rd subform = qsubFuelDipsPrior (thought I could link via DipMeterDate &
LMDate)

I was trying to use a fourth query (qsubFuelDipsPrior) to get the previous
readings from. I added a column & named it
LMDateateAdd("m",1,[DipMeterDate]) thinking that I could link LMDate to
DipMeterDate. Right now that gives me the correct last month date with the
exception of July. My DipMeterDate is 07/31/07 but the LMDate is
07/30/07???.
Now I'm totally confused. Am I even on the right track?

Thanks kindly

I assume that you don't want to hard-code the July 31 date into your
setup,
so we'll need to know a few more things.

Are you entering the August 31 date on the form? Is that when you want
the
form to show the last month's reading? and do you want to show both the
date
and fill value at that time -- and in separate textboxes or as a
concatenated string that tells you the info?

What is the form's RecordSource query?

--

Ken Snell
<MS ACCESS MVP>



Good afternoon,

I have been trying to figure out for the last three days now, how I can
show
the previous months fuel readings on the next months record. I don't
know
if
I've been looking at it too long or what but I've hit a brick wall in
trying
to get this to work. My tables are:

tblFuelTracking
FuelId - PK
FuelDate
Location (10 different locations in all)

tblFuelDips
DipId - PK
FuelId - FK
DipMeterDate
EndingDipReading
EndingDipMeter

I've tried subqueries, DLookups and anything else I could think of, but
I'm
really unclear how I can have my July 31st readings show up when I
enter
the
August 31st readings for the same location.

Could someone please give me some quidance on this before I commit
myself?

Any help would be greatly appreciated.

Thanks kindly