Why am I prompted to enter Parameter Value?

[Franky G]

Hello all,

I have a field in a query(make table) which calculates the number of days between 2 dates[TIME], then I have the query determine the value of two other fields 'Letter: LetType([TIME])' and 'range: PeriodChecker([TIME])' depending on the value of [TIME]. I do this by using a function like this;

Public Function LetType(intDays As Integer) As String
Select Case intDays
Case Is <= 180
LetType = "Less than 6 Months"
Case Is <= 210
LetType = "6M"
Case Is <= 870
LetType = "2Y"
Case Is <= 900
LetType = "30M"
Case Else
LetType = "not required"
End Select
End Function

I usually get a variety of results for the LetType Field, but I'm really only interested in the records where LetType is 6M or 30M. I use these records in a Word Mail Merge. I tried altering the query by putting 6M or 30M in the crieria box for LetType, but I then get a prompt to enter parameter value for the TIME field. I'm thinking something about circular reference..but I ain't sure at all!

Is there any way to have the query only return records where LetType is 6M or 30M? The reason I want to do this is simple; if there are no records where LetType is 6M or 30M then I'll stop the mail merge before it begins and have Access tell the user 'No data to report' - if there are better ways to do this I would glad to hear, although I'm a bit pushed for time and would rather stick with what I've done already.

Many thanks for any help,

FrankyG

 

[Mile-O]

Is there a field called TIME in your query? If not, that's why it's asking you for the value.

It's not a good practice to use a field called TIME as it is a reserved word in access i.e Time function.

 

[Franky G]

Yes, although it's calculated. The Field is called 'TIME: Date()-[DECIDD]'

I'll try changing the field name, although the query runs no problem as it is, problem only when I enter a value into the criteria box of another field - 'Letter: LetType([TIME])'

Thanks for the suggestion,

FrankyG

 

[Mile-O]

Ah! That won't work because of what you are asking it to do - you are asking it to calculate a value at the same time as it is trying to calculate off that value.

Basically, to get it to work without having to enter the parameter, you could build another query from that query (this time TIME will already be calculated).

However, it would be better to do this:

Letter: LetType(Date()-[DECIDD])

Range: PeriodChecker(Date()-[DECIDD])

 

[Franky G]

That worked! But....It took ages to run the query...like 15 mins! I'll try it again to make sure it wasn't out network but 15 mins is to long.

I put 30M in the criteria box of the 'Letter: LetType(Date()-[DECIDD])' field. the query runs quickly when the criteria box is empty...any thoughts on this?

Thanks again

FrankyG

 

[Mile-O]

It's because the query is physically having to calculate the result for each entry in the query and I'm guessing you have a lot of records.

The other way, may be to have one query that calculates the TIME field you talked about earlier.

Build a second query using the first as the source and add your Letter and Range using the new TIME field. See how that affects the speed.

 

[Franky G]

Build a second query using the first as the source and add your Letter and Range using the new TIME field. See how that affects the speed.

I tried that, but since my original query is a make table query, I don't appear to be able to use it as the source for the new query. I'm hoping there is a way to do this, otherwise I'll just get the new query to read from the newly created table. Must admit, I haven't yet had to use one query as the source for another, still learning...a lot!

FrankyG

 

[Mile-O]

Yeah, you can query off the new table.

And as the you have created a table it means you won't be storing calculated values.