Code ot working, brings out null value

[shaz123]

Can anyone see why the sql code below is not working, it works up unitl

StrSQ4 = DLookup("[D Status]", "tbl_Delupdate", "[Cylinder Barcode Label]='" & Stringy1 & "'")

But when i try to get the latest date for that Cylinder Barcode as shown below it does not bring out a value

StrSQ4 = DLookup("[D Status]", "tbl_Delupdate", "[Cylinder Barcode Label]='" & Stringy1 & "'AND [Date of D Status]=#" _
  & DMax("[Date of D Status]", "tbl_Delupdate", "[Cylinder Barcode Label] = '" & Stringy1 & "'") & "#")

 

[gemma-the-husky]

you have to look the date up separately

maxdate = Dmax("[Date of D Status]", "tbl_Delupdate", "[Cylinder Barcode Label] = '" & Stringy1 & "'")

is this what you were trying to do

 

[shaz123]

Meeage not appearing

What i was trying to do, i look up the cylinderbarcode number entered into the inputbox and too see if the latest delivery date.

if iwas to put it seperatly as you have suggested then i wanted the msgbox below to appear, however it does not appear

Maxdate = DMax("[Date of D Status]", "tbl_Delupdate", "[Cylinder Barcode Label] = '" & Stringy1 & "'")


If Trim(Maxdate & "") <> Null Then
    MsgBox "Barcode Number is already delivered. Please re-check and try again", vbCritical, "Barcode Number"

 

[gemma-the-husky]

try this, to make sure you are retrieving a date. I expected maxdate ot be a date type, but you may be using something else

dim maxdate as date

Maxdate = DMax("[Date of D Status]", "tbl_Delupdate", "[Cylinder Barcode Label] = '" & Stringy1 & "'")

msgbox("Date retrieved: " & maxdate)

 

[shaz123]

Hi it seems to be bring up the correct date now, however i when the Date fo transaction si equal to null i except my code to eidt that record, however it keeps bringing up this message, "Invalid use of Null"

When i run my code and it comes to this line "maxdate = DMax("[Date of D Status]", "tbl_Delupdate", "[Cylinder Barcode Label] = '" & Stringy1 & "'")"

The coding i have used is

  Stringy1 = InputBox("Please Enter/Scan the Cylinder Barcode Label You Wish to Deliver")
   
    If StrPtr(Stringy1) <> 0 Then
       strSQL = "SELECT * FROM [tbl_Delupdate] WHERE [Cylinder Barcode Label] = '" & Stringy1 & "' "
       StrSQ3 = DLookup("[Cylinder Barcode Label]", "tbl_Delupdate", "[Cylinder Barcode Label] ='" & Stringy1 & "'")
      StrSQ4 = "SELECT [D Status] From [tbl_Delupdate] WHERE [Cylinder Barcode Label] = '" & Stringy1 & "' "
    
    If StrSQ4 <> "" Then
        maxdate = DMax("[Date of D Status]", "tbl_Delupdate", "[Cylinder Barcode Label] = '" & Stringy1 & "'")
        MsgBox "Barcode Number is already delivered. Please re-check and try again", vbCritical, "Barcode Number"
    Else
    If StrSQ4 = "" Then
       Stringy2 = InputBox("The Cylinder/Cylinders Will Be Delivered For The Specified Date Below:-", "Spec Gas 2006, (DD / MM / YY)", Me!Text20 & "")
       Set rs = db.OpenRecordset("SELECT * From tbl_Delupdate Where [Cylinder Barcode Label] = '" & Stringy1 & "'")
   
   If rs![Cylinder Barcode Label] = Stringy1 Then
         rs.Edit
         rs![D Status] = "Delivered"
         rs![Date of D Status] = Stringy2
         rs![CustomerReturn/LindePickup] = Stringy3
         rs.Update
      
    MsgBox "Selected items have been marked as delivered for date " & Stringy2, vbInformation, "Items Changed Successfully"

  End If
  End If
  End If
  End If

If the Date fo status is empty i wanted it to carry on with Else
If StrSQ4 = "" Then and so on................

 

[gemma-the-husky]

what datatype is maxdate

if the date is not set then depends how your variable maxdate responds to a null

you could say

maxdate = nz(DMax("[Date of D Status]", "tbl_Delupdate", "[Cylinder Barcode Label] = '" & Stringy1 & "'")",0)

which will treat it as zero, if it is null.



---------------------
on reflection , why are you using dmax - this implies you have multiple cylinders with the same bar code.

if so then you need to rethink this - your dmax test will find the largest value for the date so if some have dates set, and not others, this might give you a false positive. Although you are setting the variable maxdate, you don't seem to be doing anything with it.

finally, the treatment in your recordset code will only update one item even if there are several. you could use a bulk update sql statement to set all the dates, but if you want to use a recordset, you need to loop through it until you reach eof, as using a recordset checks one record at a time.

----------
finally its a bit hard to see exactly what you are trying to do, as it is not immediately apparent how the end if statements match the if statements. it is clearer to follow the logic if you inset each if block a further tab stop.

 

[shaz123]

Ive set maxdate to a date value, i am using the DMAX function as over a period of time once a Cylinder barcode is returned it can be used again so i need to check for the the latest cylinder barcode number.

maxdate = Nz(DMax("[Time of Transaction]", "tbl_Delupdate", "[Cylinder Barcode Label] = '" & Stringy1 & "'")",0)

Keep getting a message,

"Expected : list seperator or )"

U said that i have set the variable maxdate but im not doing anyting with it, I just needed to check the lates date for that cylinder barcode label and check if the D status is equal to delivered and if it is then my message should apear to say it already in use, or if it is not then i should be able to deliver the cylnder