Search date field but multiple date fields

[Ruegen]

I have 3 payment date fields that go by payment date for a booking of the same date



the record is the booking but it might receive 3 different payments each on separate dates.

I have a form which searches by payment date - however because these are three separate fields how would I get it to show the records of any date from any one of the three fields?

i.e. text box date formatted that searches dates in all 3 fields.

Currently I have the report pointed to the textbox in the record source criteria - I am guessing this will need to be changed?

suggestions?

[Ruegen]

Might also note I have a list box of groupby which lists "trading companies" by id - each id has their own related bookings and school attached.

I need to search 3 dates in that listbox as well

so if datefield1 has 24/4/2013 or datefield2 24/4/2013 show bookings with those dates in either of those fields...

[Ruegen]

and for the subform I have this code on the listbox onclick event

Me.rptTaxInvoicesCompany.Report.Filter = "PaymentDateBanked1st=#" & Me.txtDateField & "#" & " And TradingAsID = " & Me.List0.Column(0)
Me.rptTaxInvoicesCompany.Report.FilterOn = True
Me.rptTaxInvoicesCompany.Requery

This is what I hope to use

Me.rptTaxInvoicesCompany.Report.Filter = "PaymentDateBanked1st=#" & Format(Me.txtDateField, "mm/dd/yyyy") & "# Or PaymentDepositDateBanked=#" & Format(Me.txtDateField, "mm/dd/yyyy") & "# Or PaymentDateBanked2nd=#" & Format(Me.txtDateField, "mm/dd/yyyy") & "# And TradingAsID = " & Me.List0.Column(0)


Me.rptTaxInvoicesCompany.Report.FilterOn = True
Me.rptTaxInvoicesCompany.report.Requery

[orange]

It sounds like a table design issue.
You haven't told us anything specific about your table(s) except you have 3 potential PaymentDates.

I'm guessing based on what you have said that you
-have a Client/Customer
-a Client/Customer makes a Booking on a particular date
-a Client/Customer may pay for that Booking in 1 to 3 payments (partial payments possible)

I have no idea if we're booking a Car rental or Concert seat, but I have a feeling that what is booked is also important. I'll use Event as the object of the Booking.

Seems you have (at least)

tblClient
tblEvent
tblBooking (which identifies which Client and which Event....junction)
tblBookingPayment (identifies Booking and PaymentAmt and PaymentDate)

but more details of your set up/business would be helpful.

Hope this is useful to you.

[Ruegen]

Yes normally I would add a table for bookingpayment - but not in this case.

[orange]

Why not in this case?
Please describe your Booking table with some sample data and the steps involved. Said differently, can you go through the steps of creating a Booking and receiving and recording payment for same.

This model may apply (in part at least)
http://www.databaseanswers.org/data_...ions/index.htm

[Ruegen]

Why not in this case?
Please describe your Booking table with some sample data and the steps involved. Said differently, can you go through the steps of creating a Booking and receiving and recording payment for same.

This model may apply (in part at least)
http://www.databaseanswers.org/data_...ions/index.htm

There is a tblTeacher , tblschool, tbljuncTeacher,tblbookings,tblshows,tbltradingas

basically I have a listbox that groups all the TradingAsID where payment1st is txtDateField (the payment1st is on the teacher table)

bookings get marked off when one of those fields are not null.

It's already set up and I know a way around it with passing the data to a new table.

however my concatenation is wrong - I can pull up trading as ID, I can pull up trading as id with date but not or other fields

after you click the listbox, it will display any trading companies bookings where they have the same date (on the report - any dates and fields that are not on the same date, say the deposit, are hidden using can shrink property - this hides them from the report)

[orange]

You are assuming that I know what you're talking about.
I can guess at some of these

tblTeacher , tblschool, tbljuncTeacher,tblbookings,tblshows,tbltradingas

but we are not communicating on what a Booking is, where school fits, shows etc.
Readers only know what you tell them.

With my request

Said differently, can you go through the steps of creating a Booking and receiving and recording payment for same.

I was hoping to get an overview in plain English along thiis line

ZYX Laboratories requires an employee tracking database. They want to track information about employees, the employee's job history, and their certifications. Employee information includes first name, middle initial, last name, social security number, address, city, state, zip, home phone, cell phone, email address. Job history would include job title, job description, pay grade, pay range, salary, and date of promotion. For certifications, they want certification type and date achieved.
An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst, QA Administrator). Employees can also earn certifications necessary for their job.

From RogersAccessLibrary

[Ruegen]

You are assuming that I know what you're talking about.
I can guess at some of these

but we are not communicating on what a Booking is, where school fits, shows etc.
Readers only know what you tell them.

With my request

I was hoping to get an overview in plain English along thiis line

yeah I'm confusing because it is confusing for even me. Normally I would just make a table but since I won't be doing it this time around all I need to do is filter the form to only show the results that out of 3 possible fields have an equal date to the form.

So any record that = TradingAsID

and

has a date of

date

in field

A
or
B
or
C

show records.

It should show all records with a date in any of those three fields that match. Then I just hide the fields displayed (that aren't of the same date) that are pulled up because they are on the same record of the fields with the correct dates that are pulled up.

[Ruegen]

Figured it out

I used

Me.rptCompanyTaxInvoices.Report.Filter = "(PaymentDateBanked1st=#" & Me.txtDate & "# Or PaymentDateBanked2nd=#" & Me.txtDate & "# Or PaymentDepositDateBanked=#" & Me.txtDate & "#) And TradingAsID=" & Me.listbox1.Column(0)
Me.rptCompanyTaxInvoices.Report.FilterOn = True
Me.rptCompanyTaxInvoices.Requery

I would like to know how to make a report filter to null/norecord before an event to prevent all the records from loading on form open - if anyone has a tip that would be appreciated