How do I get formulated info to table?

[Downie77]

Now that my DateDiff formula is working I would like that information to populate in my table. I have tried it with a bound box and it isn't populating.

 

[boblarson]

Are you storing the information that makes up the date diff formula anywhere? In other words - do you have both dates that you are getting the difference from stored? If the answer to that is YES then you should NOT be storing the difference. You would use the DateDiff function when you want to display it.

 

[Downie77]

But I need the difference to be stored so I can create reports off of that piece.

 

[boblarson]

But I need the difference to be stored so I can create reports off of that piece.

That's a common newbie error. A QUERY (where you can use the DateDiff function) can be used in 99.9% of the places where a table can be used. So use a query for the report and not the table.

 

[Downie77]

How do I Query a form? I was told not to use the DateDiff function on my table.

 

[boblarson]

You don't query a form. A query can be an underlying record source of a form. A query can be built that has nothing to do with a form. So, in your case, if you have two date fields in a table and you want the datediff in the query you can have a query like:

SELECT [IDField], [DateField1], [DateField2], [AnotherFieldHere], DateDiff("d", [DateField1], [DateField2]) As DiffBetweenDates
FROM YourTableNameHere

(that is the way the query looks in SQL View but in the designer you could have:

 

[DentTec]

You create QUERY based on a table, not a form. From there you can create report.

Can you be more specific of what you are trying to do ?

 

[Downie77]

Ok, so I am not going to store the DateDiff in my table because it is on the form and I will create a seperate query to get that information. My question now is I have an unbound box on my form that calculates the DateDiff but everytime I go to open my form I get a message to enter parameter value. What did I do wrong now??

 

[boblarson]

What is the actual control source of the control? I'm thinking it is a timing issue where something isn't available yet and so you would need to code for it using an NZ function or maybe something else.

 

[Downie77]

Oh; only one of my dates for the formula is in there on some of the records. I can just click through the message and get to my form. Will that be bad?? (classic newbie!! sorry!)

 

[boblarson]

Oh; only one of my dates for the formula is in there on some of the records. I can just click through the message and get to my form. Will that be bad?? (classic newbie!! sorry!)

That means we have to account for it with the control source. What do you currently have for the control source?

 

[Downie77]

=DateDiff("d",[Approved Date],[Check Date])

 

[Downie77]

In my formula the Approved Date will always be there. The Check Date is something that will be put in later.

 

[boblarson]

Okay, so how about this:

=IIF(IsNull([Check Date]), Null, DateDiff("d", [Approved Date], [Check Date])

 

[Downie77]

First it said that I was missing a para so I made it:
=IIF(IsNull([Check Date])), Null, DateDiff("d", [Approved Date], [Check Date])
Then it said I have the wrong number of arguments.....

 

[boblarson]

Sorry I missed the paren and you put it in the wrong spot. It should be:

=IIF(IsNull([Check Date]), Null, DateDiff("d", [Approved Date], [Check Date]))

 

[Downie77]

It was my best guess.... We need a sheepish grin smiley on here
I am still getting the enter parameter value error, it says TrvlTbl.Days Btwn. That is the table that loads some stuff to the form and then we will input the other information in the form to update the table. I don't have a Days Btwn column on the table so I don't know why I am getting the error.

 

[boblarson]

Are you sure you're not referencing it in some other control source? It sounds like you have it referenced somewhere (and it is not a correct way to reference it to begin with).

 

[Downie77]

I'm sure I am doing many things wrong, this is my first database. I will continue to work this out. Thanks for your help.