Trying to make a query to pick out lowercase codes

[cayham86]

Hey guys,

I have the following codes in a database but some of them have been inputted lowercase and not in uppercase, so wondering how do I make a query to display only rows where the codes are lowercase?

Help with example code would be appreciated.

 

[theDBguy]

Hi. Welcome to AWF!

If your intention is to change them to uppercase, then you can just do that to all of them, so you don't have to worry about listing the lower case entries.

 

[cayham86]

Hi. Welcome to AWF!

If your intention is to change them to uppercase, then you can just do that to all of them, so you don't have to worry about listing the lower case entries.

No I need to make a query to list them as the query will be searching multiple tables at once picking up the ones with lowercase. How do I make it pick up the lowercase ones

 

[theDBguy]

No I need to make a query to list them as the query will be searching multiple tables at once picking up the ones with lowercase. How do I make it pick up the lowercase ones

In that case, you might need a custom function to do it.

 

[cayham86]

In that case, you might need a custom function to do it.

Can't u sort a column by upper or lower case

 

[theDBguy]

Can't u sort a column by upper or lower case

No, I don't think you can. Access, by default, is case insensitive. However, you might actually be able to use this built-in function.

StrComp function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

 

[cayham86]

How would that work

 

[cayham86]

Thought u could just use come criteria in a column due to the main table the query uses has all the tables linked into one

 

[theDBguy]

How would that work

Thought u could just use come criteria in a column due to the main table the query uses has all the tables linked into one

Here's what I was thinking about. Something like...

SELECT * FROM TableName WHERE StrComp([Code], UCase([Code]), 0) <> 0

 

[cayham86]

Here's what I was thinking about. Something like...

SELECT * FROM TableName WHERE StrComp([Code], UCase([Code]), 0) <> 0

I don't want them automatically changed as not sure what table they are from so want the query to tell me what table they are from also

 

[Gasman]

I don't want them automatically changed as not sure what table they are from so want the query to tell me what table they are from also

Where is that code changing them?

 

[cayham86]

Where is that code changing them?

There isn't a code to change them as it is manually done I just need a query to pick out the lowercase codes for now

 

[CJ_London]

So have you actually tried DBG's suggestion?

as not sure what table they are from so want the query to tell me what table

Modify dbg's code to

SELECT "TblName1" as TName, * FROM TblName1 WHERE StrComp([Code], UCase([Code]), 0) <> 0
UNION SELECT "TblName2" as TName, * FROM TblName2 WHERE StrComp([Code], UCase([Code]), 0) <> 0
UNION SELECT "TblName3" as TName, * FROM TblName3 WHERE StrComp([Code], UCase([Code]), 0) <> 0
etc

 

[cayham86]

So have you actually tried DBG's suggestion?




Modify dbg's code to

SELECT "TblName1" as TName, * FROM TblName1 WHERE StrComp([Code], UCase([Code]), 0) <> 0
UNION SELECT "TblName2" as TName, * FROM TblName2 WHERE StrComp([Code], UCase([Code]), 0) <> 0
UNION SELECT "TblName3" as TName, * FROM TblName3 WHERE StrComp([Code], UCase([Code]), 0) <> 0
etc

I don't want to use SQL code I want it to be picked up from a access query

 

[cayham86]

So have you actually tried DBG's suggestion?




Modify dbg's code to

SELECT "TblName1" as TName, * FROM TblName1 WHERE StrComp([Code], UCase([Code]), 0) <> 0
UNION SELECT "TblName2" as TName, * FROM TblName2 WHERE StrComp([Code], UCase([Code]), 0) <> 0
UNION SELECT "TblName3" as TName, * FROM TblName3 WHERE StrComp([Code], UCase([Code]), 0) <> 0
etc

Plus I want it to tell me the code problems so I can manually fix them from the table drop down

 

[cayham86]

So have you actually tried DBG's suggestion?




Modify dbg's code to

SELECT "TblName1" as TName, * FROM TblName1 WHERE StrComp([Code], UCase([Code]), 0) <> 0
UNION SELECT "TblName2" as TName, * FROM TblName2 WHERE StrComp([Code], UCase([Code]), 0) <> 0
UNION SELECT "TblName3" as TName, * FROM TblName3 WHERE StrComp([Code], UCase([Code]), 0) <> 0
etc

Basically I have one table with multiple tables within it and I am trying to query it and want to add some criteria to the code column to pick out the lowercase codes and then manually fix them myself just incase some are correct

 

[CJ_London]

I'm going to drop out - you are moving the goalposts.

first the implication was one table, then it was all tables. then you added that want it to tell you the code problems. You have been provided with solutions to the first two but haven't had the courtesy to try them and provide feedback. The example you provided does not show any lower case codes to illustrate the issue (unless it is the description you are talking about) and as far as the third change is required, no examples of what you mean by code problems.

edit: just seen you have crossposted as well https://www.accessforums.net/showthread.php?t=86219&p=497074#post497074. You may not be aware of the etiquette around cross posting so please read this http://www.excelguru.ca/content.php?184

 

[cayham86]

I'm going to drop out - you are moving the goalposts.

first the implication was one table, then it was all tables. then you added that want it to tell you the code problems. You have been provided with solutions to the first two but haven't had the courtesy to try them and provide feedback. The example you provided does not show any lower case codes to illustrate the issue (unless it is the description you are talking about) and as far as the third change is required, no examples of what you mean by code problems.

edit: just seen you have crossposted as well https://www.accessforums.net/showthread.php?t=86219&p=497074#post497074. You may not be aware of the etiquette around cross posting so please read this http://www.excelguru.ca/content.php?184

The goal from the original post was to get the code column in a query to pick up any codes which are lowercase and have been incorrectly added and display then in a query

 

[cayham86]

The goal from the original post was to get the code column in a query to pick up any codes which are lowercase and have been incorrectly added and display then in a query

I'm rather stuck as all suggestions tried so far isn't working

 

[cayham86]

I'm rather stuck as all suggestions tried so far isn't working

The picture is what the codes are meant to look like bur they don't as some been inputted wrong

I'm going to drop out - you are moving the goalposts.

first the implication was one table, then it was all tables. then you added that want it to tell you the code problems. You have been provided with solutions to the first two but haven't had the courtesy to try them and provide feedback. The example you provided does not show any lower case codes to illustrate the issue (unless it is the description you are talking about) and as far as the third change is required, no examples of what you mean by code problems.

edit: just seen you have crossposted as well https://www.accessforums.net/showthread.php?t=86219&p=497074#post497074. You may not be aware of the etiquette around cross posting so please read this http://www.excelguru.ca/content.php?184