lower and uppercaps search (1 Viewer)

Siegfried

Registered User.
Local time
Today, 14:13
Joined
Sep 11, 2014
Messages
105
Dear Experts,

Is it possible to do query basis lower or uppercaps of a field in a record?
In our company a Capex is numbered with a letter but both small and upper exist:
Capex number AEANT20a (for amount under 500 USD)
Capex number AEANT20A (for amount above 500 USD)
20 being they year reference.

Best regards,

Siegfried
 

Dreamweaver

Well-known member
Local time
Today, 13:13
Joined
Nov 28, 2005
Messages
2,466
I think Access is case insensitive but things may have changed And I haven't cought up yet :)
 

Siegfried

Registered User.
Local time
Today, 14:13
Joined
Sep 11, 2014
Messages
105
Hi Mick,

Thanks for your swift response.
I consider adding a field to my table: CapexTypeID_FK, which would refer to table CapexTypeT: CapexTypeID_PK, CapexType: small or BIG.
To solve the issue incase I can't filter a and A using a query.

B. Rgds,

Siegfried
 

Micron

AWF VIP
Local time
Today, 09:13
Joined
Oct 20, 2018
Messages
3,476
What is it you want to do, exactly?
Find all where last character is 'A' not 'a' (or the reverse)?
Find all where the last character is Capitalized regardless of what it is? Is lower case regardless?

The answer might dictate which approach to take because it A vs a would be far simpler to deal with. In that case, StrComp gives you 2 strings to compare. Any old letter being capitalized means you have to extract the always (?) last character. I was thinking you might be able to look through the upper case character set for that, but that would mean a code solution rather than a convoluted query expression.

I can't recall if I've ever tried it but I would think Option Binary would make everything in a module case sensitive.
edit - compare changed to binary
 
Last edited:

Siegfried

Registered User.
Local time
Today, 14:13
Joined
Sep 11, 2014
Messages
105
What is it you want to do, exactly?
Find all where last character is 'A' not 'a' (or the reverse)?
Find all where the last character is Capitalized regardless of what it is? Is lower case regardless?

The answer might dictate which approach to take because it A vs a would be far simpler to deal with. In that case, StrComp gives you 2 strings to compare. Any old letter being capitalized means you have to extract the always (?) last character. I was thinking you might be able to look through the upper case character set for that, but that would mean a code solution rather than a convoluted query expression.

I can't recall if I've ever tried it but I would think Option Binary would make everything in a module case sensitive.
edit - compare changed to binary
Thanks Micron.
I want to be able to query both options: small and big, hence get a list of all the small capexes (AEANT19a, AEANT19b, AEANT20a, AEANT20b, AEANT20c...) and a list of all the big capexes.
 

Micron

AWF VIP
Local time
Today, 09:13
Joined
Oct 20, 2018
Messages
3,476
To me, what you just stated means you can't use StrComp because there's no second string to compare with. Unless the field is being compared to a value you provide, such as "fine me all records where Capex ends in "A", in which case you're providing a second string. My interpretation is that you are not - you need to find all records where the last character is Upper case for one search and not Upper case for the other search. Is that correct?

I suppose that the opposite is true i.e. if not Upper case then it is lower case by default? I'm thinking that a solution would involve making a decision based on the rightmost character in the field value, but that could produce unexpected results if there is no letter in that position. The big question is, is the last value always a letter?

As I see it, you need
- a dynamic WHERE clause so that you can dictate to look for Upper vs lower, OR
- 2 queries; one for Upper, one for lower OR
- a calculated field in one query that returns a result that you use to identify if Upper or lower and you use that field.

You may yet get a better idea from someone else here (wouldn't surprise me ;) ).
 

Siegfried

Registered User.
Local time
Today, 14:13
Joined
Sep 11, 2014
Messages
105
To me, what you just stated means you can't use StrComp because there's no second string to compare with. Unless the field is being compared to a value you provide, such as "fine me all records where Capex ends in "A", in which case you're providing a second string. My interpretation is that you are not - you need to find all records where the last character is Upper case for one search and not Upper case for the other search. Is that correct?

I suppose that the opposite is true i.e. if not Upper case then it is lower case by default? I'm thinking that a solution would involve making a decision based on the rightmost character in the field value, but that could produce unexpected results if there is no letter in that position. The big question is, is the last value always a letter?

As I see it, you need
- a dynamic WHERE clause so that you can dictate to look for Upper vs lower, OR
- 2 queries; one for Upper, one for lower OR
- a calculated field in one query that returns a result that you use to identify if Upper or lower and you use that field.

You may yet get a better idea from someone else here (wouldn't surprise me ;) ).
"you need to find all records where the last character is Upper case for one search and not Upper case for the other search. Is that correct?"
Yes, that's correct.
The last value is always a letter. The Capex is a form which management needs to sign for approving a purchase, when the value is lower than 500USD accounts calls it a small capex, small letter , when the amount is +500USD the capex get's a capital letter. The capex numbering unfortunately is with a letter. For example:
AEANT19b : small capex number b from 2019
AEANT19f : small capex number f from 2019
AEANT20B: big capex number B from 2020
So I will always have 2 the same capex numbers but one in small (a) and the other one in big (A). And I want to be able to query a list seperatley from the small ones and the big ones.
 

Micron

AWF VIP
Local time
Today, 09:13
Joined
Oct 20, 2018
Messages
3,476
If your table was named tbl1 and the Capex field was named fld2 then try (using your names)
Code:
SELECT tbl1.fld1, tbl1.fld2, Asc(Right(Nz(fld2,","),1)) AS [Upper] FROM tbl1
WHERE (((Asc(Right(Nz(fld2,","),1))) Between 65 And 90));
If you like the result, you could uncheck the calculated field in design view so that you don't return the numbers. I would want to look at them at the start just to see what that field is doing.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 00:13
Joined
Jan 20, 2009
Messages
12,849
It is surprising how many developers have seen this line at the top of a module yet never investigated what it meant.
Code:
 Option Compare Database

There are few other option statements that are rarely used.
 

deletedT

Guest
Local time
Today, 13:13
Joined
Feb 2, 2019
Messages
1,218
It is surprising how many developers have seen this line at the top of a module yet never investigated what it meant.
Code:
 Option Compare Database

I imagined it's a query question. Not a VBA.
Am I wrong?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:13
Joined
Feb 28, 2001
Messages
26,998
I believe it IS a VBA reference.

Suppose that you have a statement such as

Code:
IF Me.Username = "Smith" Then

If you have that line of code and the [Username] control contains "SMITH" (not mixed case) then your result will depend on the Option Compare statement.
 

deletedT

Guest
Local time
Today, 13:13
Joined
Feb 2, 2019
Messages
1,218
@The_Doc_Man I don't know you're answering me or @Galaxiom
If it's me, I was just saying Option Compare is working in vba and since the OP question is about case sensitivity in a query (in query subforum), none of the experts mentioned it.
 

Micron

AWF VIP
Local time
Today, 09:13
Joined
Oct 20, 2018
Messages
3,476
none of the experts mentioned it.
See post 5. The comment was made in the context of using StrComp in a function that a query would call. I suspect Option Binary would have no effect on a query. You would use the numeric value for binary in the function parameter.

Regardless, you are still correct because you said 'experts'.
 

Siegfried

Registered User.
Local time
Today, 14:13
Joined
Sep 11, 2014
Messages
105
If your table was named tbl1 and the Capex field was named fld2 then try (using your names)
Code:
SELECT tbl1.fld1, tbl1.fld2, Asc(Right(Nz(fld2,","),1)) AS [Upper] FROM tbl1
WHERE (((Asc(Right(Nz(fld2,","),1))) Between 65 And 90));
If you like the result, you could uncheck the calculated field in design view so that you don't return the numbers. I would want to look at them at the start just to see what that field is doing.
Thanks Micron, I tested and could query the capex number with a capital letter.(y)
I thought to get the ones with a small letter I'd change to "AS [Lower] FROM" but hat would have been too simple I guess as it doesn't work... :unsure:
 

Users who are viewing this thread

Top Bottom