Calculating in Access

[nala1]

Can anyone tell me how to go about calculating data in Access. Here's an example of what I need to do:

I have a bill date of 01/01/2007
Next due date is in 60 days (or 2 months)
How can I calculate the next due date automatically using the criteria above?

 

[Remou]

You can add days to dates. You can also use the DateAdd function.

 

[nala1]

I need to look at the cycle (e.g. 01 = 30 days, 02 = 60 days, 03 = 90 days - the system only sees the 00,01,02,03) so I need to translate the code (03) into number of days (90) then add that to calcualte my next due date. I don't know Access and I'm not a programmer...so I need help! thank You

 

[SkipVought]

Hmmmmmm.

Expr: CDate(CInt([Code])*30 + [TheDate])

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[nala1]

I receive an #error using the above. Can you explain?

 

[Remou]

Where did you put the expression? Please post the expression you used as modified to suit your application.

 

[nala1]

I'm in the form. Did properties and control source. There I went into Expression builder and selected the functions, etc. as per the instructions above.

 

[Remou]

Please post the expression you used as modified to suit your application.

 

[nala1]

=cdate(cint([cycle])*30+[lastbilldt])

 

[Remou]

Try:
=CInt([Cycle])*30+CDate([lastbilldt])

 

[nala1]

It's now giving me the same exact date as the lastbilldt.

 

[Remou]

What is the value of cycle?

 

[nala1]

Cycle is 00,01,02,03,04,06,09,
02 would be 2 months
03 would 3 months
04 would be 4 months, and so on...

 

[Remou]

And for each record, regardless of the value of cycle, you are getting lastbilldt returned as the result of the expression?

 

[nala1]

Yes.

 

[Remou]

Cycle must be a reserved word. Change the textbox to, say, txtCycle and change the expression to suit this.

 

[nala1]

Remou, That did it! Thank You so much! I appreciate all of your help!

 

[PHV]

I'd use this:
=DateAdd("m",Val([txtCycle] & ""),[lastbilldt])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nala1]

PHV...I will try that. But now I have another one. When I ran the query on the AS/400 I didn't omit any records so the data transfer to my PC (and ultimately Access) contains records I'd like to filter out of the form. How can I show say, all receipts for a selected time period BUT NOT lose any of the other data in the form? If the date is say older than 01/01/2007 I don't want it to populate the field. Thank You!