DLookup Function

[Markus Kremer]

I am trying to use the DLookup function to pull data from a query into a form
but it does not return a result and will not even show the default value. I
have structured the criteria in every way possible but just cannot seem to
get it to work. I am not getting an error message but it still returns
nothing. If I adjust in one manner I get the message "No Current Record" but
the query is fully populated so I am not sure why that is happening.

Any suggestions?

 

[Rick Brandt]

I am trying to use the DLookup function to pull data from a query
into a form but it does not return a result and will not even show
the default value. I have structured the criteria in every way
possible but just cannot seem to get it to work. I am not getting an
error message but it still returns nothing. If I adjust in one
manner I get the message "No Current Record" but the query is fully
populated so I am not sure why that is happening.

Any suggestions?

First suggestion is to post the exact syntax you're using for the DLookup().
The SQL of the query wouldn't hurt either.

 

[Markus Kremer]

=DLookUp("[Sum Of Gallons]","Fermentation Gallons Query","[FBatNum]=" &
Forms![Fermentation Data Entry Form]!FBatNum)

Above is the statement which I have modified in countless ways seeing every
possible error. What is above actually returns the error message Data Type
mismatch in Criteria even though FBatNum is set as text in both instances.

Here is the SQL of the query:
SELECT DISTINCTROW [Fermentation Data].FBatNum, Sum([QC Data].Gallons) AS
[Sum Of Gallons]
FROM [Fermentation Data] LEFT JOIN [QC Data] ON [Fermentation Data].FBatNum
= [QC Data].FBatNum
GROUP BY [Fermentation Data].FBatNum;

I am a self taught access user and I know that I have probably created a
very inefficient database but I am the only resource in my office.

 

[Rick Brandt]

=DLookUp("[Sum Of Gallons]","Fermentation Gallons Query","[FBatNum]="
& Forms![Fermentation Data Entry Form]!FBatNum)

Above is the statement which I have modified in countless ways seeing
every possible error. What is above actually returns the error
message Data Type mismatch in Criteria even though FBatNum is set as
text in both instances.

If it's text then it needs quotes around it. Your syntax would be correct
if [FBatNum] were a number field.

=DLookUp("[Sum Of Gallons]","Fermentation Gallons Query","[FBatNum]='" &
Forms![Fermentation Data Entry Form]!FBatNum & "'")

Here is the SQL of the query:
SELECT DISTINCTROW [Fermentation Data].FBatNum, Sum([QC
Data].Gallons) AS [Sum Of Gallons]
FROM [Fermentation Data] LEFT JOIN [QC Data] ON [Fermentation
Data].FBatNum = [QC Data].FBatNum
GROUP BY [Fermentation Data].FBatNum;

I am a self taught access user and I know that I have probably
created a very inefficient database but I am the only resource in my
office.

 

[Markus Kremer]

I tried that and it gives me the error No Current Record.
--
Markus Kremer

=DLookUp("[Sum Of Gallons]","Fermentation Gallons Query","[FBatNum]="
& Forms![Fermentation Data Entry Form]!FBatNum)

Above is the statement which I have modified in countless ways seeing
every possible error. What is above actually returns the error
message Data Type mismatch in Criteria even though FBatNum is set as
text in both instances.

If it's text then it needs quotes around it. Your syntax would be correct
if [FBatNum] were a number field.

=DLookUp("[Sum Of Gallons]","Fermentation Gallons Query","[FBatNum]='" &
Forms![Fermentation Data Entry Form]!FBatNum & "'")

Here is the SQL of the query:
SELECT DISTINCTROW [Fermentation Data].FBatNum, Sum([QC
Data].Gallons) AS [Sum Of Gallons]
FROM [Fermentation Data] LEFT JOIN [QC Data] ON [Fermentation
Data].FBatNum = [QC Data].FBatNum
GROUP BY [Fermentation Data].FBatNum;

I am a self taught access user and I know that I have probably
created a very inefficient database but I am the only resource in my
office.

 

[Rick Brandt]

I tried that and it gives me the error No Current Record.

Where are you using it? Is the form open at the time? Is it displaying a
record?

 

[Markus Kremer]

I am using the function within a form to access data in a Query while the
form is open displaying a record (Query is not). I am using FBatNum as the
link between the two. I have even tried to actually retrieve a specific
FBatNum record by typing it into the function but it gives me nothing (no
error, no data, nor the default value).