Select records between the fith of one month and fith of the next

T

Thorson

I have a query set up that selects the month of the field "EntryDate" based
on what is entered in the form:
[Forms]![frmCurrentInventoryDateQuery]![cboMonth]

Is it possible for the user to still enter in the month number in the form
cboMonth and have the query select records from the 5th of the month until
the 5th of the next month?
 
A

Allen Browne

So you want the current year, the month number in the combo on your form
(assumed to be a value between 1 and 12), and the 5th day of the month. This
expresion should give that:

DateSerial(Year(Date()), [Forms]![frmCurrentInventoryDateQuery]![cboMonth],
5)

Therefore, your criteria will be:

Between DateSerial(Year(Date()),
[Forms]![frmCurrentInventoryDateQuery]![cboMonth], 5)
And DateAdd("m", 1, DateSerial(Year(Date()),
[Forms]![frmCurrentInventoryDateQuery]![cboMonth], 5))
 
K

KARL DEWEY

Try this --
WHERE [EntryDate] Between
DateSerial(Year(Date(),[Forms]![frmCurrentInventoryDateQuery]![cboMonth], 5)
AND
DateSerial(Year(Date(),[Forms]![frmCurrentInventoryDateQuery]![cboMonth]-1, 5)
 
T

Thorson

Thank you, that works perfect, except for 1 thing. They also enter the year
on the form same form, so I want it to select the year from the form not just
the current year. I tried deleting the year part out of the criteria but a
warning came up saying "The expression you entered has a function containing
the wrong number of arguments"


--
Thorson


Allen Browne said:
So you want the current year, the month number in the combo on your form
(assumed to be a value between 1 and 12), and the 5th day of the month. This
expresion should give that:

DateSerial(Year(Date()), [Forms]![frmCurrentInventoryDateQuery]![cboMonth],
5)

Therefore, your criteria will be:

Between DateSerial(Year(Date()),
[Forms]![frmCurrentInventoryDateQuery]![cboMonth], 5)
And DateAdd("m", 1, DateSerial(Year(Date()),
[Forms]![frmCurrentInventoryDateQuery]![cboMonth], 5))



--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.
Thorson said:
I have a query set up that selects the month of the field "EntryDate" based
on what is entered in the form:
[Forms]![frmCurrentInventoryDateQuery]![cboMonth]

Is it possible for the user to still enter in the month number in the form
cboMonth and have the query select records from the 5th of the month until
the 5th of the next month?
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try this --
Between DateSerial([Forms]![frmCurrentInventoryDateQuery]![cboYear],
[Forms]![frmCurrentInventoryDateQuery]![cboMonth], 5) And DateAdd("m", 1,
DateSerial([Forms]![frmCurrentInventoryDateQuery]![cboYear],
[Forms]![frmCurrentInventoryDateQuery]![cboMonth], 5))

--
Build a little, test a little.


Thorson said:
Thank you, that works perfect, except for 1 thing. They also enter the year
on the form same form, so I want it to select the year from the form not just
the current year. I tried deleting the year part out of the criteria but a
warning came up saying "The expression you entered has a function containing
the wrong number of arguments"


--
Thorson


Allen Browne said:
So you want the current year, the month number in the combo on your form
(assumed to be a value between 1 and 12), and the 5th day of the month. This
expresion should give that:

DateSerial(Year(Date()), [Forms]![frmCurrentInventoryDateQuery]![cboMonth],
5)

Therefore, your criteria will be:

Between DateSerial(Year(Date()),
[Forms]![frmCurrentInventoryDateQuery]![cboMonth], 5)
And DateAdd("m", 1, DateSerial(Year(Date()),
[Forms]![frmCurrentInventoryDateQuery]![cboMonth], 5))



--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.
Thorson said:
I have a query set up that selects the month of the field "EntryDate" based
on what is entered in the form:
[Forms]![frmCurrentInventoryDateQuery]![cboMonth]

Is it possible for the user to still enter in the month number in the form
cboMonth and have the query select records from the 5th of the month until
the 5th of the next month?
Click to expand...
Click to expand...
Click to expand...
 
T

Thorson

Works perfectly thank you!
--
Thorson


KARL DEWEY said:
Try this --
Between DateSerial([Forms]![frmCurrentInventoryDateQuery]![cboYear],
[Forms]![frmCurrentInventoryDateQuery]![cboMonth], 5) And DateAdd("m", 1,
DateSerial([Forms]![frmCurrentInventoryDateQuery]![cboYear],
[Forms]![frmCurrentInventoryDateQuery]![cboMonth], 5))

--
Build a little, test a little.


Thorson said:
Thank you, that works perfect, except for 1 thing. They also enter the year
on the form same form, so I want it to select the year from the form not just
the current year. I tried deleting the year part out of the criteria but a
warning came up saying "The expression you entered has a function containing
the wrong number of arguments"


--
Thorson


Allen Browne said:
So you want the current year, the month number in the combo on your form
(assumed to be a value between 1 and 12), and the 5th day of the month. This
expresion should give that:

DateSerial(Year(Date()), [Forms]![frmCurrentInventoryDateQuery]![cboMonth],
5)

Therefore, your criteria will be:

Between DateSerial(Year(Date()),
[Forms]![frmCurrentInventoryDateQuery]![cboMonth], 5)
And DateAdd("m", 1, DateSerial(Year(Date()),
[Forms]![frmCurrentInventoryDateQuery]![cboMonth], 5))



--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.
I have a query set up that selects the month of the field "EntryDate" based
on what is entered in the form:
[Forms]![frmCurrentInventoryDateQuery]![cboMonth]

Is it possible for the user to still enter in the month number in the form
cboMonth and have the query select records from the 5th of the month until
the 5th of the next month?
Click to expand...
Click to expand...
Click to expand...
 
T

Thorson

Works perfectly! Thank you!
--
Thorson


KARL DEWEY said:
Try this --
Between DateSerial([Forms]![frmCurrentInventoryDateQuery]![cboYear],
[Forms]![frmCurrentInventoryDateQuery]![cboMonth], 5) And DateAdd("m", 1,
DateSerial([Forms]![frmCurrentInventoryDateQuery]![cboYear],
[Forms]![frmCurrentInventoryDateQuery]![cboMonth], 5))

--
Build a little, test a little.


Thorson said:
Thank you, that works perfect, except for 1 thing. They also enter the year
on the form same form, so I want it to select the year from the form not just
the current year. I tried deleting the year part out of the criteria but a
warning came up saying "The expression you entered has a function containing
the wrong number of arguments"


--
Thorson


Allen Browne said:
So you want the current year, the month number in the combo on your form
(assumed to be a value between 1 and 12), and the 5th day of the month. This
expresion should give that:

DateSerial(Year(Date()), [Forms]![frmCurrentInventoryDateQuery]![cboMonth],
5)

Therefore, your criteria will be:

Between DateSerial(Year(Date()),
[Forms]![frmCurrentInventoryDateQuery]![cboMonth], 5)
And DateAdd("m", 1, DateSerial(Year(Date()),
[Forms]![frmCurrentInventoryDateQuery]![cboMonth], 5))



--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.
I have a query set up that selects the month of the field "EntryDate" based
on what is entered in the form:
[Forms]![frmCurrentInventoryDateQuery]![cboMonth]

Is it possible for the user to still enter in the month number in the form
cboMonth and have the query select records from the 5th of the month until
the 5th of the next month?
Click to expand...
Click to expand...
Click to expand...
 
T

Thorson

I don't know if you are still getting the replies to this, if not I will have
to repost so I can get someone to reply. So the criteria you sent me works
perfectly for what I originally asked for:

Between
DateSerial([Forms]![frmCurrentInventoryDateQuery]![cboYear],[Forms]![frmCurrentInventoryDateQuery]![cboMonth],5)
And
DateAdd("m",1,DateSerial([Forms]![frmCurrentInventoryDateQuery]![cboYear],[Forms]![frmCurrentInventoryDateQuery]![cboMonth],5))

However I would like to change it a little and I can't figure out how, I'm
thinking that I made need some sort of IIf statement in the Criteria line,
but I don't know if that is even possible, I am new to this.

I want the criteria to continue to pull the records up until the 5th of the
next month (which is how it is currently setup). However, if the "DispDate"
(which is the date of the actually record) is in the first 5 days of the next
month and was also entered during the first 5 days of the next month (so the
"EntryDate" is in the first 5 days), it will come up on the previous report.

Is there anyway to get the Criteria to do what it is doing now, but check to
see if the Disposition Date is the same month as the entry date and therefore
pull that record during the actual month it took place instead of the month
before (this is only a problem during the first 5 days of the month).

My other option is to have all users wait to enter records until the 6th of
the month, but this is not very convenient.

Thanks for your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select records based on 2 dates 0
*REPOST* Select Records based on 2 dates 11
Query pulling dates from one month prior to requested month 0
*REPOST* Query Pulling dates from one month prior to requested mon 3
How to select all months from a table to include in a report 3
Data type mismatch in criteria expression 7
Date Query using Forms Parameter 1
Setting a date field using combo box 3

Top