Adding to a date

[Kat21]

I need to have my query search for a date that a user enters and then it needs to add 4 days because it will run a weekly report. Any help? Ive tried the dateadd function and it does not work.

 

[tlbroadbent]

Please show us the query and tell us what result you get when you run it. Does it return invalid results, return no results or error? With this info we may be able to help determine why it does not work. Terry Broadbent


"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Kat21]

PARAMETERS [FORMS]![Weekly]![Date] DateTime;
SELECT [DISPOSITION OF SCRAP MATERIAL].Date, [DISPOSITION OF SCRAP MATERIAL].Item, [DISPOSITION OF SCRAP MATERIAL].Quantity, [DISPOSITION OF SCRAP MATERIAL].[Vendor Error], [DISPOSITION OF SCRAP MATERIAL].[Operator Error], [DISPOSITION OF SCRAP MATERIAL].[Machine Error], [DISPOSITION OF SCRAP MATERIAL].[Scrap/Rolloff], [DISPOSITION OF SCRAP MATERIAL].[Cut Down], [DISPOSITION OF SCRAP MATERIAL].Salvage, DatePart("m",[date]) AS Expr1, DatePart("d",[date]) AS Expr2, DatePart("yyyy",[date]) AS Expr3
FROM [DISPOSITION OF SCRAP MATERIAL]
WHERE [DISPOSITION OF SCRAP MATERIAL].Date= [FORMS]![Weekly]![Date];


This is the query but I don't have the function in it. It was returning no results just a blank datasheet when it ran the query

 

[tlbroadbent]

Dates should be delimited by #. Change the where clause to this.

WHERE [DISPOSITION OF SCRAP MATERIAL].Date= "#" & [FORMS]![Weekly]![Date] & "#";
Terry Broadbent


"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[MichaelRed]

Beyond the technical issue of the dateadd function - which I'm sure Terry will show you the correct implementation.

I would STRONGLY suggest that you change the field name "Date" to something else. Ms. Access WILL permit the use of this - even though it is a key word - but there are some issues requiring you to treat it differently, to clearly indicate that it is a field in a recordset, as opposed to the current system date.

WHY are you adding "4" to a date to get a week?. This only represents the 'Week" if you do it on MONDAY. At any other time, it gives only WEIRD sets of days. There are several functions / approaches which would allow you to return the items within a given week without resorting to using a fixed integer addition to the date field.

You could, for instance get all of the records where the week of the date filed matched the week of the now function. In you query, add a calculated field ("WeekNo"?) which is ~

WeekNo: Year([YourDateField]) & "." & DatePart("w", [YourDateField])

In the criteria row, add this:

Year(Now) & "." & Datepart("ww", Now) MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Kat21]

Ok I did that so how do I get it to add 4 to the date

 

[tlbroadbent]

I agree with MichaelRed concerning the use of the column or field name "Date." It is a keyword and may cause problems in future releases of Access. It makes debugging difficult in all versions of Access because it can be so easily confused with the Date() function.

I also agree with his recommendation to use the Week function to find dates within a week. However, it is difficult to answer your question because I'm unclear about which date you want to add 4 to and how you intend to use it. Do you want to add 4 days to the date so you can search for a range of dates? Do you want to display the date that is 4 days after the selected date in the query result?

Add 4 days to a table column
DateAdd("d",4,[DISPOSITION OF SCRAP MATERIAL].Date)

Add 4 days to the date on the form
DateAdd("D",4,[FORMS]![Weekly]![Date])

This last example only works if the form is open. If the form is closed Access will ask the user for input.
Terry Broadbent


"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Kat21]

I tried this the dateadd function does not work I am putting it in the criteria for the design mode is this wrong?

 

[MichaelRed]

I'm mostly guessing but - the field "Date" - if you are not going to change it probably needs to be expressed as "[Date]" to avoid the keyword / Field name confusion / conflict.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Kat21]

Everything works fine I just need to know how to add so I can get a weekly report from the date the user enters. I have tried the functions you supplied me with but they are just returning blank data.

 

[MichaelRed]

POST THE QUERY THE WAY IT "DOESN'T WORK"! It is difficult to see what your'e doing through the veil of '... returning blank data ..." without the query SQL string!

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Kat21]

There is no need to be hasty. You were both wrong!!! Here is the proper code that works for anyone who may have the same problem I did. You will use it in your where statement

WHERE (((weekly1.Expr1)=[FORMS]![weekly]![Month]) AND ((weekly1.Expr2) Between [FORMS]![weekly]![day] And [FORMS]![weekly]![day]+4) AND
((weekly1.Expr3)=[FORMS]![weekly]![year]))