Last Record before Date

[emtenzer]

I am working on a Trial Balance with fields on a form called txtStart and txtEnd. I need to get the values in a table on the record before the txtStart date. Any Suggestions? Thank you.

 

[dhookom]

Let's try again. You have "text boxes" on a form named txtStart and txtEnd. You also must have an unnamed table with unnamed fields with at least one field being numeric and you want to do something with the field.

Can you tell use about your tables and fields? How about some sample records with the desired output?

Duane
Hook'D on Access
MS Access MVP

 

[emtenzer]

Yes, I have a form - frmTrialBalance - with a start and end date to choose.

I have a table - tblDailySummaryStoreInformation which captures information posted every business day. If I want to run the Trial Balance for 10/1/2010 to 10/31/2010, I would want to find the values in the tblDailySummaryStoreInformation for 9/30/2010 as the Starting Balance on my Trial Balance. The day before may actually be a Sunday, so it would have to just get the values from the record before the 10/1/2010 start date.

 

[dhookom]

Let's try again...
"Can you tell use about your tables and [red]fields[/red]? How about some [red]sample records[/red] with the desired output?"

A table contains fields and fields contain values. We don't know a single field name. We don't know how you record your values.

Please let us know if you don't understand what I am asking for.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya emtenzer . . .

[blue]Can you tell use about your tables and fields? [purple]How about some sample records with the desired output?[/purple][/blue]

[purple]It would be a big help![/purple]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[emtenzer]

Sorry-

a record in the table would have the following:

SummaryDate DepositsToday DepositsMTD DepositsYTD InterestEarnedToday InterestEarnedMTD InterestEaernedYTD

There are approximately 30 fields in the table. All of these are a currency data type except for the SummaryDate field which is date only. I will be using a DLookup to pull the data for the correct date, or I might need to use a DMin to pull the right date record.

 

[dhookom]

You've been asked 3 times for sample records so we can understand what you are trying to do. I just got a lot more confused regarding your table structure. I was expecting to see some type of transaction table.



Duane
Hook'D on Access
MS Access MVP

 

[emtenzer]

Sorry - those are the fields in my table. I cannot show real data because of the sensitivity of the information plus my real table has many fields.
I hope this sample with fake information will help.
SummaryDate BankDepositToday BankDepositMTD BankDepositYTD
9/24/2010 2380 30269.29 292655.22
9/25/2010 635 30904.29 293290.22
9/27/2010 50 30954.29 293340.22
9/28/2010 605.29 31559.58 293945.51
9/29/2010 0 31559.58 293945.51
9/30/2010 2410 33969.58 296355.51
10/1/2010 10012.95 10012.95 306368.46
10/2/2010 3130 13142.95 309498.46
10/4/2010 755 13897.95 310253.46
10/5/2010 1785.29 15683.24 312038.75
10/6/2010 722 16405.24 312760.75
10/7/2010 1055 17460.24 313815.75
10/8/2010 1465 18925.24 315280.75
10/9/2010 810 19735.24 316090.75
10/11/2010 475 20210.24 316565.75
10/12/2010 0 20210.24 316565.75
10/13/2010 520 20730.24 317085.75
10/14/2010 970 21700.24 318055.75
10/15/2010 2080 23780.24 320135.75
10/16/2010 1000 24780.24 321135.75
10/18/2010 605 25385.24 321740.75
10/19/2010 250 25635.24 321990.75
10/20/2010 855 26490.24 322845.75
10/21/2010 550 27040.24 323395.75
10/22/2010 3129.71 30169.95 326525.46
10/23/2010 1110 31279.95 327635.46
10/25/2010 605 31884.95 328240.46
10/26/2010 150 32034.95 328390.46
10/27/2010 600 32634.95 328990.46
10/28/2010 505 33139.95 329495.46
10/29/2010 3305 36444.95 332800.46
10/30/2010 2465 38909.95 335265.46

 

[dhookom]

I think this is what you want but I don't understand the context of the question.

SELECT *
FROM tblDailySummaryStoreInformation 
WHERE SummaryDate = (SELECT Max(SummaryDate) FROM tblDailySummaryStoreInformation D WHERE D.SummaryDate < Forms!frmTrialBalance!txtStart)

Duane
Hook'D on Access
MS Access MVP

 

[emtenzer]

That worked perfectly! Thank you very much for your time and your patience.