sql as recordset how do i use in code guys!!

[paulmcdonnell]

I have a record set on which I'm using code with Loop and EOF to through each record and change a boolean tag on or off.

This seems to work...

Dim dbs As Database
Dim rst As Recordset
Dim retvalue As String

On Error Resume Next
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(rsource)
rst.MoveFirst
Do While Not rst.EOF
With rst
.Edit
!selector = 0
.update
End With
rst.MoveNext
Loop

the rsource is a constructed SQL statement the procedure works when the SQL is simple like...
"SELECT Data_Basedata.*, Data_Basedata.company From Data_Basedata"

but when the sql become a bit more complex like...
"Select Data_Basedata.* From Data_Basedata WHERE (((Data_Basedata.Surname) Is Not Null) AND ((Data_Basedata.promssuppression)=[Forms]![Form-basedata-quick-update].[control2]))"

the loop never seems to end (the sql statements work through the QEB)

A bit stumped any ideas?


Cheers
Paul

 

[cpod]

Any nonliteral criteria must be concatenated to the sql string. Try this:

If the value from your form is a number:

"Select Data_Basedata.* From Data_Basedata WHERE (((Data_Basedata.Surname) Is Not Null) AND ((Data_Basedata.promssuppression)=" &[Forms]![Form-basedata-quick-update].[control2]))

If the value from your form is text:

"Select Data_Basedata.* From Data_Basedata WHERE (((Data_Basedata.Surname) Is Not Null) AND ((Data_Basedata.promssuppression)='" &[Forms]![Form-basedata-quick-update].[control2]))"'"

 

[paulmcdonnell]

CPOD... That was alot of help and I managed to sort out both text and numeric concatenated sql. However I can't get it right for my date fields. I know I've got to include # but I tried below to no avail, constantly getting "List Separator errors" etc.

Does anyone have any ideas on the problem...

Greatly appreciated

Paul

My string is :
SELECT Data_Basedata.* FROM Data_Basedata WHERE (((Data_Basedata.Surname) Is Not Null) AND ((Data_Basedata.promssuppression)= '" & [Forms]![Form-basedata-quick-update].[control2] & "'))

For dates I tried

SELECT Data_Basedata.* FROM Data_Basedata WHERE (((Data_Basedata.Surname) Is Not Null) AND ((Data_Basedata.promssuppression)= &"#" & '" & [Forms]![Form-basedata-quick-update].[control2] & "'&"#"& ))

 

[Jack Cowley]

I think I have this right!

SELECT Data_Basedata.* FROM Data_Basedata WHERE (((Data_Basedata.Surname) Is Not Null) AND ((Data_Basedata.promssuppression)= #" & [Forms]![Form-basedata-quick-update].[control2] & "#"))

 

[paulmcdonnell]

Practically there Jack!

That works as a statement but I use the UK date format. The wierd thing is that the sql statement returns the recordset of my UK dates only when I enter the US format as the control i.e

return all records for 10/11/01 will work if I set my control variable to 11/10/01.

How do i get this to work correctly for UK

Cheers

paul

My sql statement builds as:

SELECT Data_Basedata.* From Data_Basedata WHERE (((Data_Basedata.Surname) Is Not Null) AND (Data_Basedata.promdate)= #17/12/01#)

Put this into the QEB and it automatically changes the date part of the expression to:

#01/12/17#

Hence not returning any records... Do you know why it does this?

Stuck
Cheers
Paul




[This message has been edited by paulmcdonnell (edited 01-07-2002).]

 

[SteveA]

My experiences to date with SQL have been that dates always have to be massaged into the American format of MM/DD/YYYY to get the correct result.

While Access will display and hold dates in the non American format, my SQL always expects American formats. This occurs even though, my regional settings are set to English - Australian and my date format is d/mm/yy

I surround the Date field with the Format function and always format the date into the American format before running the SQL. It hasn't failed to date (crossing fingers).

HTH
SteveA

 

[Rich]

You might try adding the Parameters such as
PARAMETERS Forms!AccDtes![BeginningDate] DateTime, Forms!AccDtes![EndingDate] DateTime;
SELECT Deductions.*, Deductions.TrDate
FROM Deductions
WHERE (((Deductions.TrDate) Between [Forms]![AccDtes]![BeginningDate] And [Forms]![AccDtes]![EndingDate]));
HTH

 

[paulmcdonnell]

THANKS GUYS....

Things seem to be working OK...

very much appreciated...
PAUL

 

[Pat Hartman]

You can avoid the code loop entirely by running an update query.