P: n/a Christine Henderson wrote: I have a problem using the above function in the following simplified circumstance: In the lookup table called 'Klms Travelled' I have 3 fields, eg: Receiver Name Receiver Suburb Klms Distance Jones Melbourne 500 Harrison Sydney 200 Ford Brisbane 700 Jones Sydney 250 Ford Melbourne 550 In the main table named 'Clients' I have the [Receiver Name] and [Receiver Suburb] fields and based on this combination in a separate query (based on 'Clients') in a calculated field I have tried to extract the [Klms Distance] data from the lookup table as follows: Total Klms: DLookup('[Klms Distance]', 'Klms Travelled', '[Receiver Name]&[Receiver Suburb]') I have also tried variations on the criteria but I either get the value of 500 for the first record in the lookup table or nothing. Can anyone help me with a suitable criteria, or any other suggestions. Thank you, Christine In Dlookup you pass the Column field to look up/calculate on, the table name, and the filter clause (like a where clause w/o the word Where. I will assume you have a query called Klms Travelled. A field in that query is [Klms Distance]. You now need to filter. Quotes around alphas, # around dates, nothing around numbers. Ex: '[NameField] = 'Smith' '[DateField] = #' & Date & '#' 'NumberField = 123' YOu might have a variable. Ex: str = Smith '[NameField] = ' & str & '' num = 123 'NumberField = ' & num Your where/filter clause needs to be adjusted.

Hi,

Re: Dlookup / Vlookup access, VBA In excel we use the VLOOKUP function with the argument FALSE which is similar to the dlookup function in Access Some time we need to use that function with the TRUE argument that search the highest value in the table that is Less or Eq to the value I want.

This morning I have been looking all around for an answer and I feel like I am almost there, but I am getting a Syntax error. My table named 'MonthlySalesTax' is setup as below.

ID	Ship_To_City	Ship_To_State
1	Seattle	WA
2
3
4	San Francisco	CA
5
6

I want the query to auto fill the Ship_To_Ctiy and Ship_To_State if null to the above row, so Seattle and WA would fill down until it his San Francisco and CA. then San Francisco and CA would auto fill down until the next one. After researching it seemed like an update query with dlookup would work best. Here is what I have, but it comes back with and error. Any idea where the error lies or if I am approaching this incorrectly.

Dlookup In Ms Access

UPDATE MonthlySalesTax SET MonthlySalesTax.[Ship_To_State] =
DLookUp('[Ship_To_State]','MonthlySalesTax','[ID] = [ID]-1'),
MonthlySalesTax.[Ship_To_City] =

Access Vba Dlookup With String

DLookUp('[Ship_To_City]','MonthlySalesTax','[ID] = [ID]-1'),

Ms Access Vba Code

WHERE (((MonthlySalesTax.[Ship_To_State]) Is Null)) OR
(((MonthlySalesTax.[Ship_To_City]) Is Null));

Update Table Ms Access

Thanks,

Update Table Ms Access Vba Dlookup Value

Ms Access Vba Programming

Noel