Date Minues Month

[Thorson]

I currently have a query that sets the criteria of the records by a date
entered into a control on a form. I would like to create another query that
has the criteria set to the date to the date on the control form Minus 1
month (which could be 28, 29, 30 or 31 days) How do I do that?

 

[Rob Wills]

Hi,

Have you tried the Dateadd function?

Dateadd("M",-1,"20 Dec 08")

HTH
Rob

 

[Thorson]

I hadn't tried that... It works great! Thanks

 

[Thorson]

I've tried out several things and noticed a problem with the equation. If
the user types 11/30/2008 into the form it sets the parameter as 10/30/2008,
the problem is that there are 31 days in October, therefore I want it to set
the parameter as 10/31/2008. This is obviously going to change every month.

This is what I set the Critera to in the query:
<=DateAdd("m",-1,([Forms]![frmCurrentInventoryDateQuery]![txtDate]))

 

[Rob Wills]

Are you always looking for the last day of the month?

I've tried out several things and noticed a problem with the equation. If
the user types 11/30/2008 into the form it sets the parameter as 10/30/2008,
the problem is that there are 31 days in October, therefore I want it to set
the parameter as 10/31/2008. This is obviously going to change every month.

This is what I set the Critera to in the query:
<=DateAdd("m",-1,([Forms]![frmCurrentInventoryDateQuery]![txtDate]))

--
Thorson

Hi,

Have you tried the Dateadd function?

Dateadd("M",-1,"20 Dec 08")

HTH
Rob

 

[Thorson]

Yes. The last date of the month prior to the one entered into the form.
--
Thorson

Are you always looking for the last day of the month?

I've tried out several things and noticed a problem with the equation. If
the user types 11/30/2008 into the form it sets the parameter as 10/30/2008,
the problem is that there are 31 days in October, therefore I want it to set
the parameter as 10/31/2008. This is obviously going to change every month.

This is what I set the Critera to in the query:
<=DateAdd("m",-1,([Forms]![frmCurrentInventoryDateQuery]![txtDate]))

--
Thorson

Hi,

Have you tried the Dateadd function?

Dateadd("M",-1,"20 Dec 08")

HTH
Rob

:

I currently have a query that sets the criteria of the records by a date
entered into a control on a form. I would like to create another query that
has the criteria set to the date to the date on the control form Minus 1
month (which could be 28, 29, 30 or 31 days) How do I do that?

 

[Rob Wills]

write a function that you then reference from your query....

====================================
Function Thorsons_Date(dte as date) as date
dim dteTemp as date

dteTemp = Dateadd("M",-1,dte)

do until month(dteTemp) <> month(dateadd("D",1,dteTemp)
dateadd("D",1,dteTemp)
loop

Thorsons_Date = dteTemp

end function
=====================================

HTH
Rob

Yes. The last date of the month prior to the one entered into the form.
--
Thorson

Are you always looking for the last day of the month?

I've tried out several things and noticed a problem with the equation. If
the user types 11/30/2008 into the form it sets the parameter as 10/30/2008,
the problem is that there are 31 days in October, therefore I want it to set
the parameter as 10/31/2008. This is obviously going to change every month.

This is what I set the Critera to in the query:
<=DateAdd("m",-1,([Forms]![frmCurrentInventoryDateQuery]![txtDate]))

--
Thorson


:

Hi,

Have you tried the Dateadd function?

Dateadd("M",-1,"20 Dec 08")

HTH
Rob

:

I currently have a query that sets the criteria of the records by a date
entered into a control on a form. I would like to create another query that
has the criteria set to the date to the date on the control form Minus 1
month (which could be 28, 29, 30 or 31 days) How do I do that?

 

[Beetle]

That function won't quite work as posted. It would need to be;

========================================
Function Thorsons_Date(dte As Date) As Date
Dim dteTemp As Date

dteTemp = DateAdd("m", -1, dte)

Do Until Month(dteTemp) <> Month(DateAdd("d", 1, dteTemp))
dteTemp = DateAdd("d", 1, dteTemp)
Loop

Thorsons_Date = dteTemp

End Function
==========================================


However, it would be easier just to use the DateSerial function;

DateSerial(Year(Date()), Month(Date()), 0)

will return the last day of the previous month. No additional code needed.
--
_________

Sean Bailey

write a function that you then reference from your query....

====================================
Function Thorsons_Date(dte as date) as date
dim dteTemp as date

dteTemp = Dateadd("M",-1,dte)

do until month(dteTemp) <> month(dateadd("D",1,dteTemp)
dateadd("D",1,dteTemp)
loop

Thorsons_Date = dteTemp

end function
=====================================

HTH
Rob

Yes. The last date of the month prior to the one entered into the form.
--
Thorson

Are you always looking for the last day of the month?

:

I've tried out several things and noticed a problem with the equation. If
the user types 11/30/2008 into the form it sets the parameter as 10/30/2008,
the problem is that there are 31 days in October, therefore I want it to set
the parameter as 10/31/2008. This is obviously going to change every month.

This is what I set the Critera to in the query:
<=DateAdd("m",-1,([Forms]![frmCurrentInventoryDateQuery]![txtDate]))

--
Thorson


:

Hi,

Have you tried the Dateadd function?

Dateadd("M",-1,"20 Dec 08")

HTH
Rob

:

I currently have a query that sets the criteria of the records by a date
entered into a control on a form. I would like to create another query that
has the criteria set to the date to the date on the control form Minus 1
month (which could be 28, 29, 30 or 31 days) How do I do that?

 

[Thorson]

I used the Date serial Function. It looks like it works, if I encounter any
errors I will repost my problem.

Thank you for all your help!
--
Thorson

That function won't quite work as posted. It would need to be;

========================================
Function Thorsons_Date(dte As Date) As Date
Dim dteTemp As Date

dteTemp = DateAdd("m", -1, dte)

Do Until Month(dteTemp) <> Month(DateAdd("d", 1, dteTemp))
dteTemp = DateAdd("d", 1, dteTemp)
Loop

Thorsons_Date = dteTemp

End Function
==========================================


However, it would be easier just to use the DateSerial function;

DateSerial(Year(Date()), Month(Date()), 0)

will return the last day of the previous month. No additional code needed.
--
_________

Sean Bailey

write a function that you then reference from your query....

====================================
Function Thorsons_Date(dte as date) as date
dim dteTemp as date

dteTemp = Dateadd("M",-1,dte)

do until month(dteTemp) <> month(dateadd("D",1,dteTemp)
dateadd("D",1,dteTemp)
loop

Thorsons_Date = dteTemp

end function
=====================================

HTH
Rob

Yes. The last date of the month prior to the one entered into the form.
--
Thorson


:

Are you always looking for the last day of the month?

:

I've tried out several things and noticed a problem with the equation. If
the user types 11/30/2008 into the form it sets the parameter as 10/30/2008,
the problem is that there are 31 days in October, therefore I want it to set
the parameter as 10/31/2008. This is obviously going to change every month.

This is what I set the Critera to in the query:
<=DateAdd("m",-1,([Forms]![frmCurrentInventoryDateQuery]![txtDate]))

--
Thorson


:

Hi,

Have you tried the Dateadd function?

Dateadd("M",-1,"20 Dec 08")

HTH
Rob

:

I currently have a query that sets the criteria of the records by a date
entered into a control on a form. I would like to create another query that
has the criteria set to the date to the date on the control form Minus 1
month (which could be 28, 29, 30 or 31 days) How do I do that?

 

[John W. Vinson]

Yes. The last date of the month prior to the one entered into the form.

You can actually do this a bit more simply using the DateSerial function:

<=DateSerial(Year([Forms]![frmCurrentInventoryDateQuery]![txtDate]),Month([Forms]![frmCurrentInventoryDateQuery]![txtDate]),0)