Hi UA again,

I am struggling to make a (SELECT Count([Trucks]) FROM [tblTrucks]). What I need to do is count every truck in tbltrucks except
trucks containing the number 769... (this is in a query)

what I have is this, but it counts all trucks...


and then I have this, but this only counts the trucks with 777 and there are different trucks in there that need to counted as well:



what i need is a statement that counts everything except 769 trucks :-)


Thanking you in advance for your help,

Bongazi

Maybe try:



HTH

Hi HTH,

I tried your statement but I get a "Syntax Error" trying to execute the query... (attached image)

Thank you for your time.

Bongazi

Looks like I might have left a space in the expression.

How does this work for you?


If this doesn't work, you might want to try String Manipulation Functions

I am sorry - but I am getting the same error...
I will take a look at the string manipulation - maybe there is a way out of this :-)


Thank you again for your time

Bongazi

Just wondering, are trucks with 769 always excluded from Available Trucks?

If not, you might want to consider creating a query that parses the identifying number from the string of all trucks
And use the result to filter the available trucks from a form that would use this parsed string in an unbound combobox.
the RecordSource of the form could be a query that Combines the Parsed qry results with the tblTrucks
and with the Parsed Field criteria being: <>Forms!NameOfYourForm.NameOfUnboundCBO.



just an idea

Hi, thank you for the idea - but this would just complicate things. The trucks "769" are always excluded from "Available Trucks" and
should be ignored from the count...

Kind regards,
Bongazi

Please let us know how parsing the truck number works for you

lol - I am still trying to wrap my head around that.... :-)


regards

QryFindTruckNumber (or whatever you want to name it


LftParsTruck:Left([trucks],NumberOfCharacters
Parses the number of characters from the Left

Then take that result:
RtParsTruck:Right([LftParsTruck],NumberOfCharacter from the right

LftParsTruck & RtParsTruck are just aliases I came up with--Use your own understandable aliases.

Available Trucks: (SELECT Count([Trucks]) FROM [QryFindTruckNumber] where [RtParsTruck] <>769)


Does this help?

Hi again,

i did not get far with the string manipulation - but this seems to work:



it gives me the correct count - so I guess this will work?

regards,
Bongazi

As my good friend Jack always says, "There's more than one way to skin the Access cat!"

If you only need the Count of available trucks, that will do the trick!
If you need a list of the available trucks, then try Parsing.

LOL

I only need to count the available trucks excluding the "769"....
If this will do the trick - this is what I will use.

Thank you for your time and help !


Regards,
Bongazi

Hmmm.

What about

Glenn

Hi Glenn,

thank you for that - I tried your code and it works perfectly
I will use your code - simpler and most probably faster as well...

Thank you !

Regards,
Bongazi

Glenn,

Learn something from you everytime

Why would Not Like work and <>Like.. cause syntax error?

Duh! Text not number

think it's time for my nap

changed it just a bit to:



to count trucks that don't have the 769 identifier...

regards,
Bongazi

@Boganzi,

Yes, of course, you would specify whatever value you want to exclude. Also don't forget about wildcards when you are specifying criteria:

"*76?2*" would, for example specify any text value that include 76 and any text value that has 76 followed by any character followed by 2.

@Cythia,

Have a good nap. I will stand watch while you are off-duty.

Glenn

Hi Glenn,

thank you for that - I use *769* that seems to work perfectly :-)

Thank you and regards
Bongazi

PS: could I add more trucks to the exclusion list if I needed to with AND statement or so?
like (i don know the correct syntax):



regards
Bongazi

Hi Bongazi,



The query designer puts in extra parentheses so this should work also:



and so will this:



However you do have to have the outer ones:

Where (multiple criteria).

Glenn

>>However you do have to have the outer ones<<

I don't believe that is true. You do not need any parens at all (unless your criteria requires it for precedence)

Hi Walter,

I may be wrong but I think I recall having some queries that I constructed in VBA breakdown when there were multiple WHERE criteria not enclosed in at least one set of parentheses. However, I just did an empirical test and a query with multiple query using an OR did work just fine. Interesting that the QBE puts all those parentheses in.

Glenn

after a bit of tinkering - this would be the correct statement - at least it is producing the correct results:




regards,

Bongazi

The QBE does it "just in case" to force order of operations, but none are ever required that I know of.

Bongazi,

Try it without the outer parentheses and it should work equally well.

Glenn

Hi Glenn,

when I do, I get the following error (attached image)
this is the code:



Regards,
Bongazi

You still need parens around the whole subquery, but you do not need a set around the criteria.

Yeah, but you don't need to repeat WHERE: And [trucks] Not Like "*123*"

how late was I????

Bongazi,

Interesting. This reminds me of what I have experienced when constructing queries in VBA. Perhaps Walter will comment. Before my last post I tried a similar query in the QBE without the outer parentheses and it did not error out.

My pragmatic side says then, go with what works and hope that a reasonable explanation surfaces while the problem is still fresh in your mind.

Glenn

Hi Paul,
if I remove the second WHERE ... if I understand you correctly i get an error as well (attached image)


Regards,
Bongazi

Hi Glenn,
I will stick to the code that currently produces the right count:


thank you all for your help - this is really appreciated!!



Regards,
Bongazi

There's two issues here ...

1) Do you need parens around criteria in a query? No
2) Do you need parens around subqueries? Yes

Glenn asked you to remove the parens around the criteria of the subquery, but you removed them from around the entire subquery. (Your criteria didn't have any around them to remove)

Your current SQL is correct.

Hi niesz,

thank you for that - I will keep that in mind.

Kind regards,
Bongazi

@Bongazi,

You are welcome. I think I can speak for everyone who has contributed to this thread when I say we are always happy to help.

@Walter, What you said.

Glenn