Adding a date and a text value...

[musclecarlover07]

I need some help with formulas in a form. I need to add a field format with a text (number value) and get a date. I can do this in excel no problem.

My form is Storage_Data. The date field is STRG Start Date. The text field is Storage Day Qty. I need for the two fields to be added and given a date in the future for and expiration. Example if the star date was on 05/04/2012 and had 10 days before expiration i would need for the two to be added and given the date 10 days later. Im not new at access but I've done a bit an never had a need for formulas till now. Any help would be greatly appreciated.

 

[PNGBill]

Use DateAdd() to get a date so many days, weeks, months or years from another date.

This may assist http://office.microsoft.com/en-us/access-help/dateadd-function-HA001228810.aspx

 

[musclecarlover07]

That didn't seem to work. The Dateadd() doesn't work for what I'm needing unless I can use the values of 2 fields in the formula. THe 2 fields i gave have the values i need.

 

[PNGBill]

You can use the other field reference in DateAdd()
Inthe example, wouldn't you just substitute your field for Number ?

Dim Number As Integer
Number = Me.[StorageDatQty]

 

[musclecarlover07]

Honestly I'm a bit lost. Its been awhile since I have done coding, and I have never used the DateAdd formula. Coding was never really my strong suit.

Dim FirstDate As Date   
Dim IntervalType As String
Dim Number As Integer

IntervalType = "m"
FirstDate = STRG Start Date.value
Number = Storage Day Qty.value

DateAdd(IntervalType, Number, FirstDate)

Something similar to this then.

 

[PNGBill]

If your interval is day then use "d" as Interval Type.
You didn't make life easy when putting spaces in field names.

This may work.

FirstDate = Me.[STRG Start Date]
Number = me.[Storage Day Qty]

What you are doing is creating a Function to return the result of DateAdd()

In the function, you are using the values in two form controls. this is where the Me. comes in.

Are you able to post a sample database without any sensitive data on it ?

 

[musclecarlover07]

I meant to change the "m" to a "d". When i created the database it wasn't going to be a final draft it was a test. I had imported spreadsheets I just used the current headers and never got around to changing them.

No I dont have access to the database right now. Also there is sensitive data in it.

 

[PNGBill]

Do you understand how to make a Function ?

Once this done, you can test in the immediate window, then refer to the function in your form control and all should be well.

You may even be able to use =DateAdd() directly in your form control.
I will try and replicate and confirm.

 

[musclecarlover07]

Like i said I'm not sure bout placement of the code and all. If the code that we came up with is anywhere write then I will do as I always do put the code in what I think is the most logical spot test it, an keep testing till I get write. I never had any kind of formal training. I have learned what I know based on my research and testing things. I just get code an place it like I said. Most of the code I dont understand. I have deciphered and made sense out of.

 

[PNGBill]

Have a look at the sample database attached. Refer to frmTestDateAdd, modDateTest and tblDataSort

 

[musclecarlover07]

Im sorry, I can't do that tonight. I have a Mac and no program to open the DB. Monday morning I will be able to look at it and let you know if it works.

 

[PNGBill]

I have just tested and you can use

=DateAdd()

directly in a Form Control. No need for a user function and additional code.

=DateAdd("d",[DateInterval],[dmDateRcd])

This is the Control Source for the form control.
"d" you know.
[DateInterval] is the field name in the query/table your form is bound to that holds the number of days to add - 1, 2 3, or 4 etc.
[dmDateRcd] is the field name of the date we want to add so many days to.

These two fields must be in your Form Record Source.

Of course, in your case you have different field names and they have spaces.

 

[musclecarlover07]

Thank you for the help. It works. I had tried the DateAdd at first but nothing I was doing worked. I tried it this morning an it kept giving me dates in 2023. I then exit the DB and tried again it now works.

 

[PNGBill]

Glad to assist

 

[musclecarlover07]

Ok it worked but I just ran in to a MAJOR problem. It doesnt save the data into the datasheet.

I used the DateDiff function to subtract the Expired date and Todays date to tell me how many days are remaining till expiration date. The problem is it doesnt save it into the datasheet. If it doesnt save into the datasheet then my querys become useless. An the days remaining is a vital piece to this database.

 

[PNGBill]

Why do you need to save this value ?

You can include DateAdd() in your query and have the result when ever you need it.

Most advice is to not save Calculated Fields ie keep them in queries and Forms only.

It is possible to add a record to a table or update a field on a table by using an Append Query or Update Query. DateAdd() can work with both of these.

If you have StartDate and IntervalDays both in a table, then really there is no need to also have Finish Date saved.

 

[musclecarlover07]

1. I need it to be saved because we work off the the remaining days and expiration date. Out of 4 fields 2 of them have formulas.
I have:
Start Date
Expirations
Authorized
Days Remaining

Start date and Authorized are 2 fields that we will physically input data in to
Expirations and Days Remaining have formulas

Start date and Authorized will produce the Expired date
Then todays date and the Expired date will produce the Remaining Days
We work off the remaining Days and it needs to update everyday. I do not know how to use formulas in queries. I need to have a list box displaying the Remaing days an this is what we base our work off of. If there is way to show all this information with out it saving to the datasheet but also updateing when suppose to then please help me. I do not have a basic understaning of most thingsi n access. I know what I have taught myself an most of the code I know, i dont know the reasoning or what not on how it works. I just know what to change to make it work. My database cant be used with out the above formulas. Its the heart an soul of our work.

 

[PNGBill]

Maybe you need to be aware of a few basics.

Forms, often, are based on Queries, not tables.

Forms can have Bound and Unbound Controls.
A Bound Control is mostly a Text Box Control bound to a Field on a Table or Query. It can also be a List Box or a Combo Box or Check Box but they are all Form Controls that hold data from a Table or Query.
Unbound Controls are the same as Bound, except they are not Bound to a field on a Table or Query.
Unbound Controls are handy for displaying data you like to see but need not be saved. eg, ExpiredDate and DaysRemaining.

How can you have the above two displayed on Forms and Reports when ever they are needed ?

Two basic ways.
You can include Calculated Fields in a Query. (check help for this)
Field1 Field2 Field3:[Field1]+[Field2]
20 10 30
When you have a calculated field in a query that is the data source for a Form or Report, it is the same as if the field was saved in a table but it is recaculated each time.

You can have unbound controls on your form or report and refer to the value they hold.
This means, with what you have done so far, the value of ExpiredDate can be used, while the form is open.
Also, if you can calculate the DaysRemaining, just like ExpiredDate was calculated.
DaysRemaining = (StartDate + DateInterval)-Today'sDate
ie the EndDate is not required for this result.
To do this, you would create your own Function or use the expression builder.

Just one note... Try and keep your field names consistant as it appears they change on your different posts. This could lead to misunderstandings and incorrect advice - which of course, wouldn't happen, sometimes

 

[musclecarlover07]

Ok so I got everything I need. I need for the query to filter based on the expression. I have a query with the actual expressions. I have 3 Expr: Expr, Expr1, Expr2. If I but a criteria of Between 30 and 45 (thats what I need to filter for one query) it gives me no results, I figured that much cause that Expr if todays date. If I put Beween 30 and 45 on the other Expr and hit run it brings up "Enter Parameter Value" for both Expr and Expr1. I created another query and under field I have Expr Expr1 (as opposed to the acutal formula) and it gives me a mismatch error.

The query with the actual expressions in the main query the form opperates of. I need multiple queries with different Criteria. I need queries with the following Criteria:
46+
30-45
-numbers to 29

If you need any more information or for something to be explained further please let me know. I aprreciate the help so far. My database is coming along.

 

[PNGBill]

Is this a new issue ? What query are you referring to ?
Why not have three queries ?

If the query is the Data Source for a Form or Report, then you have a number of choices.

But first explain your task as well as your problem.