Dlookup - access can't find parameter (1 Viewer)

[MattioMatt]

Hello,

I'm trying to use a Dlookup in a query. There is a reason for this (despite me knowing this is not the right way of doing it), but due to the data being premature and working through issues I'm currently basing my queries on imported data from excel. I currently do not have relationships between my tables, as for now I have just imported the data from Excel sheets and will start to build the relationships at a later stage as the priority has been getting some/limited reporting in place.

I want to look up the status in another table seperate from the table I'm using in my query using the ID's in both tables as the lookup. However each time I try to do so I'm given the following error. The expression you entered as a query parameter produced this error: 'Product Security Dashboard cannot find the name 'Qry_Data_Export.APID' you entered in the expression'

Here's the expression I'm using:

Expr1: DLookUp("[tblAssessments].[SummaryStatus]","[tblAssessments]","[tblAssessments].[APID]=[Qry_Data_Export].[APID]")

I'm currently creating a query called Qry_Data_Export, within that query I'm pulling data from one table 'tblInventory'. I'd like to look up the status within another table called 'tblAssessments'. I used the below but as described above it is giving me an information warning only and I can by pass by pressing ok and then can view the output of the query to which Expr1 is blank.

If I change the expression to the below it works fine, albeit it populates all the corrects with the status from the one record.

Expr1: DLookUp("[tblAssessments].[SummaryStatus]","[tblAssessments]","[tblAssessments].[APID]=1001")

 

[plog]

I'm trying to use a Dlookup in a query. There is a reason for this...

Please explain the reason for this because it makes no sense. Or was the part about not having set up relationships yet the reason? If so, horrible reason, bring tblAssessments into your query.


As for why your Dlookup isnt' working, look at what information you are giving it:

 DLookUp("[tblAssessments].[SummaryStatus]","[tblAssessments]","[tblAssessments].[APID]=[Qry_Data_Export].[APID]")

The first thing you pass it is the field name you want to lookup. The second is the table to look that field up in. Think about that for a second, DLookup is so dumb it requires you to explicitly tell it what table and field to use.

Now look at the third thing you pass it--criteira. You can't pass it a completely seperate table and field name and hope it knows what you are talking about. It knows you are working in [tblAssessments] (because you explicitly told it you were) so whenever it encounters a field in the criteria it assumes that field belongs to the table you told it to work with. It does not have access to any other table/query. It does have access to any real values you pass it (like that 1001 in the working version you have).

Again, DLookup isn't the way to go. Bring your table into the query and JOIN it.

 

[bob fitz]

Perhaps:

Expr1: DLookUp("[tblAssessments].[SummaryStatus]","[tblAssessments]","[tblAssessments].[APID]= " & [Qry_Data_Export].[APID])

But why not just join the tables with the field APID

 

[arnelgp]

firstly, what is the name of your base
Query/Table, since you have the Expr1 on
different table and looking up a value
on yet another different query?!

if you also have APID on the base table/query
you can use it.

your dlookup may not guarantee it will return
the value you want. since qry_data_export can
have multiple 1001 APID.

 

[MattioMatt]

There is a logical explanation which is I need to be able to include a date at which data was uploaded into the DB so I can have a historical view. I can only see one way to do this at the moment which is to include an 'uploadedon' field which is current set to 'Now()' so it is populated to all the records added to the tables.
This again is so I can caculate the totals based on a monthly basis as that is the format I need my reporting to be in.

I currently have a number of Excel docs which I upload to tables now. Whilst they have logical relationships in some instances (example being the above), in order for me to catch the totals from last month to this month I was adding all of the records as an append query. What I've found is if I add tblAsessments into the query I'm currently trying to build the amount of records doubles - even if I create a relationship between the two tables inside the query.

 

[plog]

Sounds like you need to build a sub-query then to determine the correct value for each APID.

 

[MattioMatt]

firstly, what is the name of your base
Query/Table, since you have the Expr1 on
different table and looking up a value
on yet another different query?!

if you also have APID on the base table/query
you can use it.

your dlookup may not guarantee it will return
the value you want. since qry_data_export can
have multiple 1001 APID.

The base tables are:
tblAssessments & tblInventory

The query I'm working on with tblInventory is Qry_Data_Export.

I am pulling selected fields from tblInventory with the exception that I need one field from tblAssessments.

 

[arnelgp]

Can u use this

Expr1: (select t1.summarystatus from tblassessments as t1 where t1.apid=[apid])

 

[MattioMatt]

Can u use this

Expr1: (select t1.summarystatus from tblassessments as t1 where t1.apid=[apid])

That is then prompting me to enter a parameter value

 

[arnelgp]

Meaning apid is not on your base table/query.

 

[MattioMatt]

It's definitely there. It's the first field in the I've selected from tblInventory in the query (Qry_Data_Export)

Field: APID
Table: tblInventory

 

[arnelgp]

Please show the whole query ur building.

 

[MattioMatt]

Thanks for all the help so far from everyone on this post.

I've manged to tweak the query and use bob fitz advice to link the tables in the query with the APID and also the DateUploadedOn - therefore marking this thread as resolved.