vb6 ms.access database: Variable not defined

[Octo Siburian]

I have a database in ms.access,
I want to retrieve data in the database is based on today's date. I have a query like this.
but when I run this program, it is error.
'Variable not defined'

QuerySelect = "SELECT a.DN, b.DIN, c.PIN, c.UserName, b.Clock, d.ItemName " & _
             "FROM ((ras_Device a INNER JOIN ras_AttRecord b ON a.DN=b.DN) " & _
             "LEFT JOIN ras_Users c ON b.DIN=c.DIN) " & _
             "LEFT JOIN ras_AttTypeItem d ON b.AttTypeId=d.ItemId " & _
             "WHERE b.DIN=c.DIN AND '" & Format(b > *error,block area.Clock, "mm/dd/yyyy") & "'='" & _
             Format(um_TglDanJamSkrg, "mm/dd/yyyy") & "'"
 Set rsDataRAS = da_GetRs(QuerySelect)

Can anyone hel me to fix this error

Regards,
Octo

[MMcCarthy]

OK, firstly dates have to be enclosed with # marks rather than single quotes. So try this ..

QuerySelect = "SELECT a.DN, b.DIN, c.PIN, c.UserName, b.Clock, d.ItemName " & _
             "FROM ((ras_Device a INNER JOIN ras_AttRecord b ON a.DN=b.DN) " & _
             "LEFT JOIN ras_Users c ON b.DIN=c.DIN) " & _
             "LEFT JOIN ras_AttTypeItem d ON b.AttTypeId=d.ItemId " & _
             "WHERE b.DIN=c.DIN AND #" & Format(b > *error,block area.Clock, "mm/dd/yyyy") & "#=#" & _
             Format(um_TglDanJamSkrg, "mm/dd/yyyy") & "#"
 Set rsDataRAS = da_GetRs(QuerySelect)

If that doesn't work then my guess is we need to look at these two values.

Format(b > *error,block area.Clock, "mm/dd/yyyy")

and

Format(um_TglDanJamSkrg, "mm/dd/yyyy")

We'll need more information on where these values are coming from.

Mary

[Octo Siburian]

Dear Mary

thanks for the replies .. but it's still compile error: variable not defined and the same position on 'b.Clock at WHERE ..
#" & Format(b.Clock, "mm/dd/yyyy") & "#=#" & _
Format(um_TglDa nJamSkrg, "mm/dd/yyyy") & "#"

Regards,
Octo

[Octo Siburian]

Format(b.Clock, "mm/dd/yyyy")

b.Clock from field in database ms.access table ras_AttRecord b field: Clock

Format(um_TgldanJamSkrg, "mm/dd/yyyy")

um_TgldanJamSkr g from definition of function

Public Function um_TgldanJamSkrg() As Date
Dim rsJam As New Recordset
Dim strSQL As String
  
    strSQL = "SELECT GETDATE() AS jam"
    Set rsJam = da_GetRecord(strSQL)
  
    um_TgldanJamSkrg = rsJam!jam
  
rsJam.Close
Set rsJam = Nothing

End Function

[MMcCarthy]

You can't call a function just by inserting the function name in your code. Try this...

Dim tmpDate As Date

    tmpDate = um_TglDanJamSkrg()

    QuerySelect = "SELECT a.DN, b.DIN, c.PIN, c.UserName, b.Clock, d.ItemName " & _ 
             "FROM ((ras_Device a INNER JOIN ras_AttRecord b ON a.DN=b.DN) " & _ 
             "LEFT JOIN ras_Users c ON b.DIN=c.DIN) " & _ 
             "LEFT JOIN ras_AttTypeItem d ON b.AttTypeId=d.ItemId " & _ 
             "WHERE b.DIN=c.DIN AND Format(b.Clock, 'mm/dd/yyyy')=#" & _ 
             Format(tmpDate, "mm/dd/yyyy") & "#" 
    Set rsDataRAS = da_GetRs(QuerySelect)

[Octo Siburian]

Dear Mary,
Many Thanx, but now.. Run-time error
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
that caused the data is not available, right?

Regards,
Octo

[MMcCarthy]

It sounds like either your function or your query is not returning any records.

Add Debug.Print QuerySelect just before the Set rsDataRAS = da_GetRs(QueryS elect) line. This will print out the query in the "Immediate" window. If you can't tell visually what the problem is then copy and paste the query into a new query and try running it.

[Octo Siburian]

Dear Mary,

I've added Debug.Print QuerySelect before Set rsDataRAS = da_GetRs(QueryS elect) line and then I copy and paste the query in the Form_Load, there is no issue, then the desired data are not artifacts in the database, right?

Regards,
Octo

[MMcCarthy]

then I copy and paste the query in the Form_Load

I don't understand what you mean by this. I meant for you to create a new query in query design. Change the layout to sql and paste in the query. Then run it and see what the results are.

then the desired data are not artifacts in the database, right?

Not sure what you mean by this.

[Octo Siburian]

Dear Mary,

I have try & that's work, Thanks so much for your help..
GBU

Regards,
Octo