Function in WHERE clause (1 Viewer)

[DemonDNF]

I've tried everything I can think of and can't figure this out.


This works when RUN from SQL view:

SELECT PartID
FROM Inventory
WHERE [Barcode]=#[U]2014-02-13 02:20:42[/U]#;

It returns the correct record.


This returns a syntax error:

SELECT PartID
FROM Inventory
WHERE [Barcode]=#[U]ReturnBarcode()[/U]#;

I can't figure the proper syntax to embed # on each side of my date function.

Barcode is DateTime on the table, Default Value=NOW().

This is the test code I'm using:

Public dtBarcode As Date
 
Public Function ReturnBarcode() As Date
  ReturnBarcode = dtBarcode
End Function
 
Private Sub Form_Current()
  Part.BackColor = LabelColour
End Sub
 
Private Sub Form_Load()
  Dim lngPartID As Long
 
  dtBarcode = DateValue("2014-02-13") + TimeValue("02:20:42")
 
  lngPartID = DLookup("[PartID]", qryInventoryGetPartID)
End Sub

I'm adding a ZIPped copy of this test code. It has just the gutted database, form and queries.

Robert


EDIT: dtBarcode has been changed to Public in code above and new ZIP.


EDIT SOME MORE: Adding search strings to help me in 5 years when I'll have the same darned problem:

SQL embed date function where clause statement # pound sign

 

[namliam]

1) your DLookup is broken...
lngPartID = DLookup("[PartID]", qryInventoryGetPartID)
should be
lngPartID = DLookup("[PartID]", "qryInventoryGetPartID")

2) Public functions (and variables) belong in a public module, create one and place your public function/variable there.

3) the ## only serve to identify a value as a date, when you have a function that is defined as a date variable, you dont need them period. Simply remove them.

 

[DemonDNF]

1. TYTYTYTYTYTY I can't believe I totally missed those quotes in the threads I found.

2. I know. They were in a module called modPublicVariables but I had moved them there to make things easier for you guys to see my screwup.

3. D'oh...


Man, I went through the gazillion threads on here about these issues I'm been having and I still come up and ask stuff that's already been answered.

Robert

 

[namliam]

/duh moments, we all done them, we all been there.