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.
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.