Lookup not right

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have a table that I want to lookup whether a date exists in a particular
field... Inventory database where when you 'Start Inventory' an update
query will append a new record for each Active Item in the items table to the
inventory table... but the code should check if the [InvDate] field has a
particular date in it...

dDate is the date of the inventory ( there should be duplicate dates being
there are more than one product) but It is not detecting the existance of
such date and creating more than one inventory record for the date and item.

Here is the code...

If Not Nz(DLookup("[DateID]", "tblInventory", "[InvDate] = #" & dDate &
"#")) > 0 Then
sSQL = "Insert into tblInventory (InvID, InvDate) Select " _
& "tblInventoryItems.InvID, " & Format(dDate, "\#mm/dd/yyyy\#") _
& " FROM tblInventoryItems WHERE tblInventoryItems.ActiveID=1;"

DoCmd.RunSQL sSQL
End If

Any ideas would be great,

Ernst.
 
Thanks... Works great.

Klatuu said:
If Not IsNull(DLookup("[DateID]", "tblInventory", "[InvDate] = #" & dDate &
"#") Then

Ernst Guckel said:
Hello,

I have a table that I want to lookup whether a date exists in a particular
field... Inventory database where when you 'Start Inventory' an update
query will append a new record for each Active Item in the items table to the
inventory table... but the code should check if the [InvDate] field has a
particular date in it...

dDate is the date of the inventory ( there should be duplicate dates being
there are more than one product) but It is not detecting the existance of
such date and creating more than one inventory record for the date and item.

Here is the code...

If Not Nz(DLookup("[DateID]", "tblInventory", "[InvDate] = #" & dDate &
"#")) > 0 Then
sSQL = "Insert into tblInventory (InvID, InvDate) Select " _
& "tblInventoryItems.InvID, " & Format(dDate, "\#mm/dd/yyyy\#") _
& " FROM tblInventoryItems WHERE tblInventoryItems.ActiveID=1;"

DoCmd.RunSQL sSQL
End If

Any ideas would be great,

Ernst.
Click to expand...
Click to expand...
 
Back
Top