SQL Statement Problem in form

[mdmarney]

Private Sub Form_Current()

Set rs = CurrentDb.OpenRecordset("Select * from tbl_Attendants where Parish or Group ID = " & me!Parish or Group ID & &quot

rs.MoveLast

Me.tx_Count_Box_2 = rs.RecordCount

End Sub *************
M. MARNEY

http://www.tgw.com

 

[mdmarney]

Boy, I'm in too much of a hurry...

The code posed above will not work. Any suggestions? *************
M. MARNEY

http://www.tgw.com

 

[WildHare]

After a brief second, I realized you have a field called "Parish or Group ID", right?

Try embedding QUOTES around it in the criteria:

Parish or Group ID = [red]'[/red]" & me!Parish or Group ID & "[red]'[/red]&quot

Your bigger problem might be the use of the keyword OR in your field name, not to mention the spaces. You'll probably end up needing:

[Parish or Group ID] = [red]'[/red]" & me![Parish or Group ID] & "[red]'[/red]&quot

As a rule of thumb, don't use embedded spaces in field names, and try to stay away from reserved or key words in Access, such as NAME, DATE, OR, AND, etc etc etc...

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[mdmarney]

Thanks for the tip. I changed to the following after editing the field names.
Set rs = CurrentDb.OpenRecordset("Select * from tbl_Attendants where [Parish_Group_ID] = '" & Me![Parish_Group_ID] & "'&quot

This did not work. Any thoughts *************
M. MARNEY

http://www.tgw.com

 

[WildHare]

First, DO YOU KNOW that there should be a match or two?

Second of all, the syntax LOOKS correct, assuming you have textual data in your field.

Do you need a semi at the end? I don't often use raw SQL for record sets, but I think that you need a semi to end a sql clause when used like this... ??

where [Parish_Group_ID] = '" & Me![Parish_Group_ID] & "' [red] ; [/red] &quot

to the final result resolves to something like:

[Parish_Group_Id] = 'FOO';




Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[mdmarney]

Hmmm....
There are matches.
I was using this code from another person posting, but something you said made me wonder...
These are both long int fields.
Is that the problem?
*************
M. MARNEY

http://www.tgw.com

 

[WildHare]

Yes. Numbers don't need quotes around them...

Oy Vey...

Jim
Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm