Hi,

How do I query using this format ("lastname,firstname")?

thanks

Hi,

How do I query using this format ("lastname,firstname")?

thanks

Show your sql.

Select LastName & ", " & FirstName AS FullName from tblContacts order by LastName

Select LastName & ", " & FirstName AS FullName from tblContacts order by LastName

I would doubt a field name would have a comma in it. The poster is referring to the WHERE clause (I would have thought).

I would doubt a field name would have a comma in it. The poster is referring to the WHERE clause (I would have thought).

Field name with a comma???? Where do you see this?

WHERE:
Same method would apply.....

Field name with a comma???? Where do you see this?

WHERE:
Same method would apply.....

I think the poster wasn't asking how to concatenate/build an sql string simply because he/she put the string in brackets. So my guess was it could be a parameter, maybe?

I think the poster wasn't asking how to concatenate/build an sql string simply because he/she put the string in brackets. So my guess was it could be a parameter, maybe?

If thats the case then:
WHERE ((LastName & ", " & FirstName) = "Inet, VBA")

I use a Dialogue Form with just two fields and then create a Criteria:



Private Function ArtistsDialogueCriteria() As String
With CodeContextObject
ArtistsDialogueCriteria = "[Surname] like '" & .[Field1] & "*" & "' and [FirstName] like '" & .[Field2] & "*" & "'"
End With
End Function


Then a button and use the Criteria within the OnClick Event:

DoCmd.OpenForm "Artists Details", , , ArtistsDialogueCriteria, , acWindowNormal

Simon

I use a Dialogue Form with just two fields and then create a Criteria:



Private Function ArtistsDialogueCriteria() As String
With CodeContextObject
ArtistsDialogueCriteria = "[Surname] like '" & .[Field1] & "*" & "' and [FirstName] like '" & .[Field2] & "*" & "'"
End With
End Function
Then a button and use the Criteria within the OnClick Event:

DoCmd.OpenForm "Artists Details", , , ArtistsDialogueCriteria, , acWindowNormal

Simon

I've noticed that Simon does like this CodeContextObject :) hehe!

Its a bit addictive. The real reason I use it is that I have to deal with images so wherever there Stock you have to be able to double click and bring up the image. This goes through Stock, Exhibitions, Locations, Editions, Sales, Client Interest etc so users expect to be able to get to an Image. It was easier to write one script that having one the was Form dependent.

The other reason was, correct me if I'm wrong - having modules on Forms or Reports going back to Access 1997 was considered inefficient so all the scripts are in Modules which is great as I can trace every bit code.

Simon

Hello,

thank you very much for this very enlightening views from everybody. I've attached my sql design. The one with the circle is actually what i'm referring too.

thanks again everyone.

Hello,

thank you very much for this very enlightening views from everybody. I've attached my sql design. The one with the circle is actually what i'm referring too.

thanks again everyone.

Wrap it in quotes:

"John, Doe"

Its a bit addictive. The real reason I use it is that I have to deal with images so wherever there Stock you have to be able to double click and bring up the image. This goes through Stock, Exhibitions, Locations, Editions, Sales, Client Interest etc so users expect to be able to get to an Image. It was easier to write one script that having one the was Form dependent.

The other reason was, correct me if I'm wrong - having modules on Forms or Reports going back to Access 1997 was considered inefficient so all the scripts are in Modules which is great as I can trace every bit code.

Simon


lol It was just something I noticed. It's a pretty useful method.

Wrap it in quotes:

"John, Doe"


It was actually wrapped in quotes and that is the error I get.

Unless I need to go to specsavers, looking at that screen shot I don't see any quotes. All I see is this:

John, Doe

Unless I need to go to specsavers, looking at that screen shot I don't see any quotes. All I see is this:

John, Doe

:) don't worry I can assure you I did put in the quotes before I even tried posting this issue here.

:)

:) don't worry I can assure you I did put in the quotes before I even tried posting this issue here.

:)


So that was a deceiving screenshot you put up then lol. I've just tested it and it works. Attach your db

So that was a deceiving screenshot you put up then lol. I've just tested it and it works. Attach your db


On the field "assigned to", I tried editing the names it worked, I wonder if it has something to do with the way I imported it from excel spreadsheet?

Your thoughts?

Thanks

On the field "assigned to", I tried editing the names it worked, I wonder if it has something to do with the way I imported it from excel spreadsheet?

Your thoughts?

Thanks

It could be that it contained spaces at the end. Maybe next time try using a wild character. Such as:
Like "*John, Doe*"

Also before importing and before saving it's good to Trim() your value.

It could be that it contained spaces at the end. Maybe next time try using a wild character. Such as:
Like "*John, Doe*"

Also before importing and before saving it's good to Trim() your value.

Thanks for the advice. So when do I trim? before or after I have imported the data?

Thanks for the advice. So when do I trim? before or after I have imported the data?

Before saving, unless you're absolutely positive that the data being saved would not contain spaces.