Dlookup? (1 Viewer)

jsmullins87

New member
Local time
Today, 05:22
Joined
Jan 2, 2020
Messages
1
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:
 

vba_php

Forum Troll
Local time
Today, 07:22
Joined
Oct 6, 2019
Messages
2,884
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.
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?
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.
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?
 
Last edited:

plog

Banishment Pending
Local time
Today, 07:22
Joined
May 11, 2011
Messages
11,611
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?
 

SammyDyck

New member
Local time
Today, 05:22
Joined
Feb 4, 2020
Messages
3
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
1580833673843.png
. Please point out what my syntax error is so I won't make it again.

Many Thanks
Sam
 

Attachments

  • Lookup.jpg
    Lookup.jpg
    20.9 KB · Views: 79
  • queryFields.jpg
    queryFields.jpg
    6.6 KB · Views: 71
  • tblDates.jpg
    tblDates.jpg
    14.6 KB · Views: 68

Gasman

Enthusiastic Amateur
Local time
Today, 12:22
Joined
Sep 21, 2011
Messages
14,038
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
 

plog

Banishment Pending
Local time
Today, 07:22
Joined
May 11, 2011
Messages
11,611
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.
 

SammyDyck

New member
Local time
Today, 05:22
Joined
Feb 4, 2020
Messages
3
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
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)
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:22
Joined
Sep 21, 2011
Messages
14,038
You would get the first record that matches the date value you are supplying?
Which is the same in VlookUp I believe.?
 

Users who are viewing this thread

Top Bottom