using a specific query depending on a date range

[snipe]

Hey guys,

So close to being done with this stage of the project.

What kind of code/query can I do to do the following:

If the date being used to enter data on (selected via combobox on main form) is in the range of Quarter "X", then use the value of [QrtlyAvg] from query Quarter "X-1".

Obviously I can do a DLookup to get the value from the query, but how can I get the program to chose the right query. The 4 options are SlspInfo_Q1, SlspInfo_Q2, SlspInfo_Q3, SlspInfo_Q4.

When a salesperson uses sick or vacation time, we multiply their hours by the quarterly average from the previous quarter. This value needs to get pulled in to the input form where hours, commission, etc gets typed in.



Was I detailed enough?

[orange]

I recommend some vba code, probably Select Case construct
see

[snipe]

Hmm, I guess I could use that and pretty much say that if the date is within Q4, use Q3 avg; if within Q3, use Q2 avg, etc...

I'll give this a go tonight or tomorrow.

[snipe]

Ok what is wrong with this statement:

Me.HourlyRate = DLookup("HrlyAvg", "SlspInfo_Q3_2", "Employee= " & "Me!Text34")

me.hourlyrate is an unbound textbox on the entry form
HrlyAvg is a field in the query SlspInfo_Q3_2
Employee is a field in the same query, formatted as text
me!text34 is a textbox on the entry form that pulls the employee code, in the format of AB12345, formatted as text.

I am getting run-time error 2471: the expression produced this error:'me!text34'

[orange]

You do not need quotes around
"Me!Text34"

Since Employee is string, I think you need

Me.HourlyRate = DLookup("HrlyAvg", "SlspInfo_Q3_2", "Employee= '" & Me!Text34 & "'")

[snipe]

Me.HourlyRate = DLookup("HrlyAvg", "SlspInfo_Q3_2", "Employee= '" & Me!Text34 & "'")

Ah that's what I missed. I'm playing hooky today, but I'll make this change on Monday.

Appreciate it orange!

[snipe]

Works great! Thanks orange!

[orange]

Happy to help