dlookup

  • Thread starter Thread starter Simon Harris
  • Start date Start date
S

Simon Harris

Hi All,

I have a form that is used to record gas/electric meter readings for a
caravan park. I need to display the previous readings on the same form.


I have a query called QRY_PreviousReadings that gets the previous readings
as follows:
SELECT TOP 1 TBL_Readings.idInvoice, TBL_Readings.Electric_Reading,
TBL_Readings.Gas_Reading, TBL_Allocations.idAllocation
FROM TBL_Parks INNER JOIN (((TBL_Customers INNER JOIN TBL_Allocations ON
TBL_Customers.IDCustomer = TBL_Allocations.CustomerID) LEFT JOIN
TBL_Readings ON TBL_Allocations.idAllocation = TBL_Readings.AllocationID)
INNER JOIN TBL_Pitches ON TBL_Allocations.PitchID = TBL_Pitches.idPitch) ON
TBL_Parks.idPark = TBL_Pitches.ParkID
ORDER BY TBL_Readings.idInvoice DESC;

Note that the query when run with a where clause matching the DLookup filter
returns the expected values.


I've then set the control source of my previous readings field to this:
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
[idAllocation])
Which returns nothing!

I've also tried this:
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
me.idAllocation)
Which returns #Name?

Note that me.idAllocation refers to a form field which has the current
IDallocation - This stays the same for the duration of the persons stay on
the park.


Any help will be much appreciated!

Many Thanks,
Simon.



--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4652 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com/pro for free now!




--
 
Try using;
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
Forms!frmFormName!idAllocation)

Where frmFormName is the name of the form that idAllocation is on.

Hope this helps.
 
Thanks for your post Andrew. I should have mentioned that this is on a
sub-form. Your code works without error when I load the subform on its own,
but when I load the parent form, I get this in the field where the value
should be:

#Name?

I guess I just need to reference the form field differently. Any ideas?

Thanks,
Simon.

Andrew Tapp said:
Try using;
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
Forms!frmFormName!idAllocation)

Where frmFormName is the name of the form that idAllocation is on.

Hope this helps.

Simon Harris said:
Hi All,

I have a form that is used to record gas/electric meter readings for a
caravan park. I need to display the previous readings on the same form.


I have a query called QRY_PreviousReadings that gets the previous
readings
as follows:
SELECT TOP 1 TBL_Readings.idInvoice, TBL_Readings.Electric_Reading,
TBL_Readings.Gas_Reading, TBL_Allocations.idAllocation
FROM TBL_Parks INNER JOIN (((TBL_Customers INNER JOIN TBL_Allocations ON
TBL_Customers.IDCustomer = TBL_Allocations.CustomerID) LEFT JOIN
TBL_Readings ON TBL_Allocations.idAllocation = TBL_Readings.AllocationID)
INNER JOIN TBL_Pitches ON TBL_Allocations.PitchID = TBL_Pitches.idPitch)
ON
TBL_Parks.idPark = TBL_Pitches.ParkID
ORDER BY TBL_Readings.idInvoice DESC;

Note that the query when run with a where clause matching the DLookup
filter
returns the expected values.


I've then set the control source of my previous readings field to this:
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
[idAllocation])
Which returns nothing!

I've also tried this:
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
me.idAllocation)
Which returns #Name?

Note that me.idAllocation refers to a form field which has the current
IDallocation - This stays the same for the duration of the persons stay
on
the park.


Any help will be much appreciated!

Many Thanks,
Simon.



--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4652 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com/pro for free now!




--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4661 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com/pro for free now!
Click to expand...
Click to expand...
 
OK, try using a reference to the subform instead;
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
Forms!frmFormName!frmSubFormName.Form!idAllocation)

Where frmFormName is the name of the form and frmSubFormName is the name of
the subform.

Hope this helps.

Simon Harris said:
Thanks for your post Andrew. I should have mentioned that this is on a
sub-form. Your code works without error when I load the subform on its own,
but when I load the parent form, I get this in the field where the value
should be:

#Name?

I guess I just need to reference the form field differently. Any ideas?

Thanks,
Simon.

Andrew Tapp said:
Try using;
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
Forms!frmFormName!idAllocation)

Where frmFormName is the name of the form that idAllocation is on.

Hope this helps.

Simon Harris said:
Hi All,

I have a form that is used to record gas/electric meter readings for a
caravan park. I need to display the previous readings on the same form.


I have a query called QRY_PreviousReadings that gets the previous
readings
as follows:
SELECT TOP 1 TBL_Readings.idInvoice, TBL_Readings.Electric_Reading,
TBL_Readings.Gas_Reading, TBL_Allocations.idAllocation
FROM TBL_Parks INNER JOIN (((TBL_Customers INNER JOIN TBL_Allocations ON
TBL_Customers.IDCustomer = TBL_Allocations.CustomerID) LEFT JOIN
TBL_Readings ON TBL_Allocations.idAllocation = TBL_Readings.AllocationID)
INNER JOIN TBL_Pitches ON TBL_Allocations.PitchID = TBL_Pitches.idPitch)
ON
TBL_Parks.idPark = TBL_Pitches.ParkID
ORDER BY TBL_Readings.idInvoice DESC;

Note that the query when run with a where clause matching the DLookup
filter
returns the expected values.


I've then set the control source of my previous readings field to this:
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
[idAllocation])
Which returns nothing!

I've also tried this:
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
me.idAllocation)
Which returns #Name?

Note that me.idAllocation refers to a form field which has the current
IDallocation - This stays the same for the duration of the persons stay
on
the park.


Any help will be much appreciated!

Many Thanks,
Simon.



--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4652 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com/pro for free now!




--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4661 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com/pro for free now!
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your post Andrew. I should have mentioned that this is on a
sub-form. Your code works without error when I load the subform on its own,
but when I load the parent form, I get this in the field where the value
should be:

#Name?

I guess I just need to reference the form field differently. Any ideas?

Thanks,
Simon.

Andrew Tapp said:
Try using;
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
Forms!frmFormName!idAllocation)

Where frmFormName is the name of the form that idAllocation is on.

Hope this helps.

Simon Harris said:
Hi All,

I have a form that is used to record gas/electric meter readings for a
caravan park. I need to display the previous readings on the same form.


I have a query called QRY_PreviousReadings that gets the previous
readings
as follows:
SELECT TOP 1 TBL_Readings.idInvoice, TBL_Readings.Electric_Reading,
TBL_Readings.Gas_Reading, TBL_Allocations.idAllocation
FROM TBL_Parks INNER JOIN (((TBL_Customers INNER JOIN TBL_Allocations ON
TBL_Customers.IDCustomer = TBL_Allocations.CustomerID) LEFT JOIN
TBL_Readings ON TBL_Allocations.idAllocation = TBL_Readings.AllocationID)
INNER JOIN TBL_Pitches ON TBL_Allocations.PitchID = TBL_Pitches.idPitch)
ON
TBL_Parks.idPark = TBL_Pitches.ParkID
ORDER BY TBL_Readings.idInvoice DESC;

Note that the query when run with a where clause matching the DLookup
filter
returns the expected values.


I've then set the control source of my previous readings field to this:
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
[idAllocation])
Which returns nothing!

I've also tried this:
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
me.idAllocation)
Which returns #Name?

Note that me.idAllocation refers to a form field which has the current
IDallocation - This stays the same for the duration of the persons stay
on
the park.


Any help will be much appreciated!

Many Thanks,
Simon.



--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4652 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com/pro for free now!




--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4661 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com/pro for free now!
Click to expand...
Click to expand...



--
 
See my previous post.

Simon Harris said:
Thanks for your post Andrew. I should have mentioned that this is on a
sub-form. Your code works without error when I load the subform on its own,
but when I load the parent form, I get this in the field where the value
should be:

#Name?

I guess I just need to reference the form field differently. Any ideas?

Thanks,
Simon.

Andrew Tapp said:
Try using;
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
Forms!frmFormName!idAllocation)

Where frmFormName is the name of the form that idAllocation is on.

Hope this helps.

Simon Harris said:
Hi All,

I have a form that is used to record gas/electric meter readings for a
caravan park. I need to display the previous readings on the same form.


I have a query called QRY_PreviousReadings that gets the previous
readings
as follows:
SELECT TOP 1 TBL_Readings.idInvoice, TBL_Readings.Electric_Reading,
TBL_Readings.Gas_Reading, TBL_Allocations.idAllocation
FROM TBL_Parks INNER JOIN (((TBL_Customers INNER JOIN TBL_Allocations ON
TBL_Customers.IDCustomer = TBL_Allocations.CustomerID) LEFT JOIN
TBL_Readings ON TBL_Allocations.idAllocation = TBL_Readings.AllocationID)
INNER JOIN TBL_Pitches ON TBL_Allocations.PitchID = TBL_Pitches.idPitch)
ON
TBL_Parks.idPark = TBL_Pitches.ParkID
ORDER BY TBL_Readings.idInvoice DESC;

Note that the query when run with a where clause matching the DLookup
filter
returns the expected values.


I've then set the control source of my previous readings field to this:
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
[idAllocation])
Which returns nothing!

I've also tried this:
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
me.idAllocation)
Which returns #Name?

Note that me.idAllocation refers to a form field which has the current
IDallocation - This stays the same for the duration of the persons stay
on
the park.


Any help will be much appreciated!

Many Thanks,
Simon.



--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4652 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com/pro for free now!




--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4661 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com/pro for free now!
Click to expand...
Click to expand...



--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4661 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com/pro for free now!
Click to expand...
 
Thanks - It now works without error via the form subform. The dlookup now
looks like this:
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
Forms!frmMeterReading!SubForm_Invoices!idAllocation)
But, it still does not return the expected value (Seems to be returning
null)

I've just worked out why...Think I've got some reworking to do, heres the
process at the moment:

1) User selects a location, date and type of bill (Gas/Electric) and hits
'Enter Readings'. This creates a record in tblReadings for each customer
matching the selected criteria
2) The recordsource of my subform is then changed to the ne rows created in
tblreadings
3) The subform is then requeried...Heres the problem, the dlookup is looking
for the previous reading, which by my workings is based on getting the last
record adding to tblReadings matching the current rows idAllocation...Which
of course following steps 1 and 2 is empty, I'm actually getting the latest
EMPTY record.

I guess what I need is the 2nd to last entered record.

Anyone know how I could change the following query to do this?

SELECT TOP 1 TBL_Readings.idInvoice, TBL_Readings.Electric_Reading,
TBL_Readings.Gas_Reading, TBL_Allocations.idAllocation
FROM TBL_Parks INNER JOIN (((TBL_Customers INNER JOIN TBL_Allocations ON
TBL_Customers.IDCustomer = TBL_Allocations.CustomerID) LEFT JOIN
TBL_Readings ON TBL_Allocations.idAllocation = TBL_Readings.AllocationID)
INNER JOIN TBL_Pitches ON TBL_Allocations.PitchID = TBL_Pitches.idPitch) ON
TBL_Parks.idPark = TBL_Pitches.ParkID
where idallocation = 7
ORDER BY TBL_Readings.idInvoice desc;

I've tried changing the order by clause to ASC instead of DESC but it seems
access orders the source data first, rather than after applying the where
clause, because I get two completelty different records.

Thanks!
Simon.

Andrew Tapp said:
OK, try using a reference to the subform instead;
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
Forms!frmFormName!frmSubFormName.Form!idAllocation)

Where frmFormName is the name of the form and frmSubFormName is the name
of
the subform.

Hope this helps.

Simon Harris said:
Thanks for your post Andrew. I should have mentioned that this is on a
sub-form. Your code works without error when I load the subform on its
own,
but when I load the parent form, I get this in the field where the value
should be:

#Name?

I guess I just need to reference the form field differently. Any ideas?

Thanks,
Simon.

Andrew Tapp said:
Try using;
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
Forms!frmFormName!idAllocation)

Where frmFormName is the name of the form that idAllocation is on.

Hope this helps.

:

Hi All,

I have a form that is used to record gas/electric meter readings for a
caravan park. I need to display the previous readings on the same
form.


I have a query called QRY_PreviousReadings that gets the previous
readings
as follows:
SELECT TOP 1 TBL_Readings.idInvoice, TBL_Readings.Electric_Reading,
TBL_Readings.Gas_Reading, TBL_Allocations.idAllocation
FROM TBL_Parks INNER JOIN (((TBL_Customers INNER JOIN TBL_Allocations
ON
TBL_Customers.IDCustomer = TBL_Allocations.CustomerID) LEFT JOIN
TBL_Readings ON TBL_Allocations.idAllocation =
TBL_Readings.AllocationID)
INNER JOIN TBL_Pitches ON TBL_Allocations.PitchID =
TBL_Pitches.idPitch)
ON
TBL_Parks.idPark = TBL_Pitches.ParkID
ORDER BY TBL_Readings.idInvoice DESC;

Note that the query when run with a where clause matching the DLookup
filter
returns the expected values.


I've then set the control source of my previous readings field to
this:
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
[idAllocation])
Which returns nothing!

I've also tried this:
=DLookUp("[gas_reading]","QRY_PreviousReadings","[idAllocation] = " &
me.idAllocation)
Which returns #Name?

Note that me.idAllocation refers to a form field which has the current
IDallocation - This stays the same for the duration of the persons
stay
on
the park.


Any help will be much appreciated!

Many Thanks,
Simon.



--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4652 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com/pro for free now!




--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4661 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com/pro for free now!
Click to expand...
Click to expand...
Click to expand...



--
 
Back
Top