Dlookup?

Fairly new to Access and trying to figure out how to do some things in my database that are fairly easy in excel.

I have one field in a query that returns the amount of time spent on a task. I want to have a second field that returns a grade based on the hours spent. I tried using a table that held the number of hours that needed to be spent on said task to earn a certain grade and then using a Dlookup function in the expression builder but kept getting syntax errors.

Is there an easier expression I could use? :banghead:

jsmullins87 said:

I have one field in a query that returns the amount of time spent on a task. I want to have a second field that returns a grade based on the hours spent.

Click to expand...

it really depends on where the data is at that you're using for comparison. can you give any more info on where all the relevant data is at? e.g. - which data is in which tables?

jsmullins87 said:

I tried using a table that held the number of hours that needed to be spent on said task to earn a certain grade and then using a Dlookup function in the expression builder but kept getting syntax errors.

Click to expand...

that's exactly the right way to do it. store the number of hours needed to get certain grades in a seperate table called a "lookup table" what kind of errors did you see? in this man's humble opinion, write your functions in sql by hand and ditch that ridiculous expression builder.

by the way, welcome to the place. can you post an intro thread in the "introduce yourself" forum so people can say hi?

You don't use Dlookups in queries. Instead you JOIN data sources together and bring in the data you need.

https://www.w3schools.com/sql/sql_join.asp

Your JOIN sounds special, in that you will not have exact matches. Can you post a sample of your database? Or the names of your tables and their fields?

I am working in a database that at the present time only has tables and queries. The reason that I am using a table for the dates which will eventually go into a date range (Between expression) so I can change the dates in one place versus revising 8 queries each time I want to change my searches.

My goal is to use dates that are in tblDates (see attachment for format) to query data in qADPPHasBeenAudited (see attachment for field names). The current example query that I am building (as a learning example) is qDLOOKUPtest (see attachment - DesignView) and the SQL is

SELECT qADPPHasBeenAudited.PlanNumber, qADPPHasBeenAudited.AuditDate
FROM qADPPHasBeenAudited
WHERE (((qADPPHasBeenAudited.AuditDate)=DLookUp("[Date_Type]","tblDates","Date= #date#")));

I receive this error when I attempt to run the query in its current state . Please point out what my syntax error is so I won't make it again.

Many Thanks
Sam

You need to concatenate variables with the sql string.
However as stated you should use joins in queries not Dlookups.?

Date is also a reserved word, so best change yours to something more meaningful?

HTH

Since your whole approach is incorrect can you please demonstrate what you want to achieve with data? Please provide 2 sets:

A. Starting data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you want your query to end up with when you feed it the data in A.

Again, 2 sets of data that tie together.

Gasman said:

You need to concatenate variables with the sql string.

Code:

DLookUp("[Date_Type]","tblDates","Date= #" & date & "#")));

However as stated you should use joins in queries not Dlookups.?

Date is also a reserved word, so best change yours to something more meaningful?

HTH

Click to expand...

Follow-on question for clarification - My data table three fields with six records - how do I make sure that I get the correct record in the date field, addressing the corresponding field in the Data_Type field? (my analogy from Excel would be the VLookup but that may be my problem here)

You would get the first record that matches the date value you are supplying?
Which is the same in VlookUp I believe.?