Declare a constant (Field / Expression?) to be used in a query?

[Cosmos75]

I have a query with these fields

HireDate: From tbldate - Date (Short)
MonthHire: Format([HireDate], “m”)
2Day: Date()
Month2Day: Format([2Day],”m”)
DiffMonth:[Month2Day]-[MonthHire]

I want the query to prompt the use to enter 2Day instead of having 2Day be equal to Date(). How do I do that?

I’ve tried 2Day:[Enter Date], and it returns 2Day and Month2Day correctly but any formulas based on Month2Day won’t work?
(There's more to this query but I didn't see the need to list everything else)


I guess I am asking how do I declare a constant (that the user enters) to be used in a query without VBA?? Is that what I am trying to do??

 

[RichMorrison]

Not sure I understand what you are trying to do but that never stops me from answering.

If you want a default value for 2Day but you want to give the user the option of changing the value;

create a form with a textbox (call it txt2Day) and a command button to open the query,
make the default value of txt2Day = Date(),
in the query put "Forms![YourForm]![txt2Day]" where you want the value.

Also, I don't think this is right
<<
DiffMonth:[Month2Day]-[MonthHire]
>>

You want DateDiff("m",HireDate,2Day)

RichM

 

[Cosmos75]

That's what I thought...

create a form with a textbox (call it txt2Day) and a command button to open the query

I had thought of doing that but wanted to find out if there was a way to have the standard Access inputbox that usually pops up when you use a parameter query (where you filter on a field in a query).

Using 2Day:[Enter Date] does bring that inputbox up and it is in the query as a constant, and like I said before the query expression Month2Day: Format([2Day],”m”) DOES return the correct result just that anything that references Month2Day is incorrect. I even tried to replace and expressions that has [Month2Day] in it with Format([2Day],”m”) but no luck.
Any idea why?

You want DateDiff("m",HireDate,2Day)

Well, the two return different values,

Say
HireDate: 08/11/2001
2Day: 09/16/2002

DiffMonth:[Month2Day]-[MonthHire] gives me 1.

DateDiff("m",HireDate,2Day) gives me 13

Oh well, guess that I'll have to use a form to store that constant.
Thanks, RichMorrision!

 

[raskew]

Substitute the DatePart() function, which returns an integer, for your format()
solutions and it should solve your problem.

Here's a query applied against Northwind's Employee table which you can
use to test on. Wasn't absolutely sure whether you wanted to calculate
the difference in months without regard to years, or the total number of
months between the two dates, so it's shown both ways.

SELECT LastName, FirstName, HireDate, DatePart("m",[HireDate]) AS 
MonthHire, [Enter a  Date] AS 2Day, DatePart("m",[2Day]) AS Month2Day, 
[Month2Day]-[MonthHire] AS DiffMonth, 
DateDiff("m",[2Day],[HireDate]) AS TotMonths
FROM Employees;

 

[RichMorrison]

Well, it looks like I didn't understand the question.

I thought you wanted to avoid the pop up prompt for "2Day".

Next, you wrote
<<
Well, the two return different values,

Say
HireDate: 08/11/2001
2Day: 09/16/2002

DiffMonth:[Month2Day]-[MonthHire] gives me 1.

DateDiff("m",HireDate,2Day) gives me 13
>>

OK. Is 13 months what you want? Looks right to me.

Finally, you wrote
<<
anything that references Month2Day is incorrect.
>>

In what way ?

SITH, (surprised if that helps)
RichM

 

[Cosmos75]

Tell Access what data type the parameter is

I figured it out! You can do it by either using a form or by a parameter (??) in the query! It has to do with telling the query what the parameter is (Date, Number...). If you right-click (Access 2000) in the query design grid, choose paramters and type in the parameter (if you are refering to a textbox on a form, you'll need to enter it as you fo in the query - [Forms]![FormName]![txboxName]), you then need to choose what type of data it is.

The problem in my query was a calculation - [2day]-[ProbationDate]. That was causing the error was Access didn't know what data type [2Day] was and hence couldn't perform the calculation. I think, since I just used that calculation to see if that gave an error, which it did. All other calculations follow that calculation.

What is this, not really a parameter (entered in the criteria section under a field).

Instead, it's an expression 2day:[Enter Date], right? Is it a parametered expression?

 

[Cosmos75]

Original Thread that started all this!

Here's the original thread that started all this.
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=34248

I know that the calculations are a bit convoluted, but I don't have time smooth them out.

They should at least give SaraMegan an idea of what the calculations need to do and maybe she can find time to tweak them!

 

[SaraMegan]

Thanks, everyone, it finally works!

Aaaahhhhh.