SQL code error

[Lou_Reed]

Consider the following SQL code

SELECT [tblPersonnel].[PersonnelID], [FirstName] & " " & [LastName] AS FullName, emailaddress FROM tblPersonnel ORDER BY [FirstName] & " " & [LastName] WHERE FullName IS NOT NULL;

The last part WHERE FullName IS NOT NULL



was added to the SQL statement to prevent the combo box from selecting or allowing blank spaces. The semi-colon was moved to the end of the line when the new SQL code as shown above was added.

Now I get a error whenever I click on the combo box to access its choices of employee names.

The error is shown in the attached snagit screen files. It claims there is a syntax error, but all I did was insert the SQL code fraction (above) and then place the semi-colon at the end of the now longer line of code. I do not see this as causing an error.

What is going on?

Any help appreciated. tanks in advance.

Respectfully,

Lou Reed

[June7]

FullName is not a field in tblPersonnel, it is calculated in the query. Cannot reference field generated by calculation in the query in the WHERE clause. Reference LastName instead.

[CJ_London]

you would need to use

WHERE [FirstName] & " " & [LastName] is not null

but it never will be null because as a minimum, you've added a space. So try

WHERE [FirstName] is not null and [LastName] is not null


Also, the order for query sections is

SELECT
FROM
WHERE
ORDER BY

and you have the last two the wrong way round

[Lou_Reed]

Okay, looks like you are right. I am use a calculated filed in he WHERE clause ad it fails.

But isn't

WHERE [FirstName] & " " & [LastName] is not null also a calculated field?

I use FullName and you use [FirstName] & " " & [LastName]. So how do you get way with it and I do not?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[CJ_London]

I'm not getting way with anything. I'm just following the rules of how sql is interpreted

[June7]

Lou, Ajax said the same thing I did - cannot reference calculated field and to eliminate the error would have to repeat the concatenation expression. Also, Ajax said that your concatenation expression would not filter as you want because of the space. Then provides alternative. Not getting away with anything.

I failed to notice the incorrect order of clauses.

[ArviLaanemets]

Hi

In addition to anwers from others:

Consider the following SQL code

SELECT [tblPersonnel].[PersonnelID], [FirstName] & " " & [LastName] AS FullName, emailaddress FROM tblPersonnel ORDER BY [FirstName] & " " & [LastName] WHERE FullName IS NOT NULL;

Lou Reed

Access queries can't operate very well with empty fields, so use Nz() where you suspect them - otherwise some records may be lost in result table.

SELECT [tblPersonnel].[PersonnelID], Trim(Nz([FirstName],"") & " " & Nz([LastName],"")) AS FullName, emailaddress FROM tblPersonnel ORDER BY [FirstName] & " " & [LastName] WHERE Trim(Nz([FirstName],"") & " " & Nz([LastName],""))<>"";
or check FirstName and Lastname separately, as it was advised before.

[Bulzie]

Lou you should use the query designer more, it helps keep the code error free.

For the RowSource of your combo box, click on the ... to go to the query builder, it should put things as it needs to avoid errors. One of the fields is FullName: [FirstName] & " " & [LastName], sort on this field and in the criteria box of this field put Is Not Null, then run it to see if it gives what you need. you can also put it in SQL code mode to see what that looks like.

[Lou_Reed]

I apologize for the use of the of the term "getting away with it". I should have said yours works and mine does not. I was just curious as to why I could not use FullName because it was a calculated field and
it seemed the you did use a calculated field.

I just thought that maybe my syntax was more explicit than yours on using a calculated field, but either way it should be wrong. We are using as calculated field! So I will use your syntax and I am very thankful for it. Please do not misunderstand. I just do not see any difference in the two - at first glance; that is all.

I must really apologize if you mistook the tone of the post. It was not intended. The minute you said you are using a calculated field, I knew what the error was.

Thanks so much for your help.

Respectfully,

Lou Reed

[Lou_Reed]

It now works in that it does not crash with a runtime error of 2465 on that line. It crashes on the next line with a runtime error of 2162.

The reason was my brain lock when you said control. It was just a question of terminology. Once I new what you said it was simple.

However, as I said it now crashes on the next line of the VBA (see attached screenshot).

I am also including the db in one of the attached fields.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed


Respectfully,


Lou Reed

[Lou_Reed]

Here are the files that should have been put in the previous post.

Respectfully,


Lou Reed

[CJ_London]

no idea what runtime error 2162 is. Provide a description and perhaps it will tell me what the problem is

[Lou_Reed]

The error read is pop-up dialogue box

"A Macro set to one of the current field's properties failed because of an error in a FindRecord action argument"

I do not know what that means. I hope someone on the forum does, because it would seem that the VBA code should work.

Is there a work-around for this? Some code modification to avoid the runtime error 2162.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[CJ_London]

Regret I don't know what it means either. But if you google the error message in it's entirety you will find several links to other forums where other developers have had the same message - perhaps one of those will give you a clue as to what is wrong with your application.

[Lou_Reed]

I have tried that there is no definitive answer. They suggest you might try this or that.........? Nothing clear.I will keep trying.

Respectfully,

Lou Reed