Finding records inbetween dates

[Elle]

I really need some help. I have a form that needs a look up dates from the beginning of the month to the end of the month in a planned delivery field. I need the results to bring up the information on my list box. Can someone help me.. I have been working on this for days. If someone does have an answer can you give me detailed instructions as I am not so good at this.

Thank you oh.. so ever much

Elle

 

[Cosmos75]

Base your form on a query and in the date field you wish to use as criteria for that field in the query

Between [StartDate] and [EndDate]

This will promt the user for the Start Date and End Date.

OR

You can put two textboxes (txtStartDate & txtEndDate) on your form and enter the dates there and use Between [Forms]![FormName]![txtStartDate] and [Forms]![FormName]![txtEndDate]

Are you looking to always only show records for one month (July only, August only, December only)?

If you are confused, let me know what fields your tables have, what you want on your form and what you want displayed on the form. I'll try to find time to make a sample database.

 

[ddmcn]

Another option is to set the following criteria in a query that pulls your [PlannedDeliveryDate]:

(datepart("m",[PlannedDeliveryDate]=[Month])

In this scenario the user would be prompted for the number of the month (i.e. September = 9, October = 10, etc.) The query results would only contain records with a PlannedDeliveryDate falling in that month.

Hope this helps.

 

[Cosmos75]

Great Minds think alike!

ddmcn,

I asked,

Are you looking to always only show records for one month (July only, August only, December only)?

If she answered YES, I would have given the same answer you did! Cool!

 

[ddmcn]

Cos,

I noticed your question...that's partly what made me think of that solution...

 

[Rich]

What about the Year? or does it not matter?

 

[Cosmos75]

Rich asked

What about the Year? or does it not matter?

You could also add another expression in the query and subsitute "y" for "m".

 

[ddmcn]

Good question, Rich.

In order to include the year, you would need to variables input.

(datepart("yyyy",[PlannedDeliveryDate]=[Year])

so the complete statement would contain both datepart functions connected by AND

The prompt for the year would come after the prompt for the month.

Thanks!

 

[Elle]

Thank you so ever much for helping....I am still having problems.. maybe if I share what I need to do maybe someone can help me through the whole process.

I have 4 tables that need to ref. each other and bring it up on a form that retrieves in between dates and calculate the quanity of orders along with the sales order price with the name who we are selling to and the sales rep name for each month for our accounting office to make up reports..

Please see attached diagram for what I need help with.. Thanks for all the help...

Elle

 

[ddmcn]

Mademois elle

I didn't see any attachment...did you forget?

Dennis

 

[Elle]

attachment

Sorry I didn't check the formats.. Here is the attachment