Query not working anymore? 1

[Kobayashi]

I have the below code which used to work perfectly well in Access 97 some years ago but now doesn't, either in Access 97 or 2003.

The problem is, once the query is created it just doesn't retrieve the recordset? If I go into the query after it has been created and retype one of the values then it does work?

I've been out of the Access game for a few years so apologies if this is something daft...

I've received a lot of help from this site in the past (indeed, this very code probably was provided some years ago), so I'm hoping for the same again...

Regards,

Adrian


If ObjectExists("Queries", "qryACK_QDF_Confirms") = True Then
MyDb.QueryDefs.Refresh
MyDb.QueryDefs.Delete "qryACK_QDF_Confirms"
MyDb.QueryDefs.Refresh
End If

Where = Null
Where = Where & " AND [Next Approver]='" + MyRs![To] + "'"
Where = Where & " AND [AckSent] = Null"

'Select Records
Set MyQdf = MyDb.CreateQueryDef("qryACK_QDF_Confirms", _
"Select * From [tblConfirmsHistory]" & ("WHERE " + Mid(Where, 6) & ";"))


The SQL looks like this once the query has been created:

SELECT *
FROM tblConfirmsHistory
WHERE (((tblConfirmsHistory.[Next Approver])='Person's Name') AND ((tblConfirmsHistory.Acksent) Is Null));

 

[PHV]

Where = Null
Where = Where & " AND [Next Approver]='" & Replace(MyRs![To], "'", "''") & "'"
Where = Where & " AND [AckSent] Is Null"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Kobayashi]

PHV,

Many thanks, I really appreciate your help. However, although the SQL has been tidied up and now looks like:

SELECT *
FROM tblConfirmsHistory
WHERE [Next Approver]='Adrian Zanelli' AND [AckSent] = Null;

...the query is still not returning any results? If I go into the query grid and reselect the field from the combo box or re-type the value of the criteria, and then refresh, it does return the results?

This is the same as what happened before?

It's a new database (I've imported all of the objects into it) so should be okay?

Any ideas?

Many thanks,

Adrian

 

[Kobayashi]

PHV,

Sorry, it's early and I'm not with it yet!

I forgot to change the =Null to is null!

I've now changed that too and it is working perfectly! I can't believe I missed that but sometimes you can't see the wood for the trees!

Many thanks for your help!