UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query Expression, Access 2010    
 
   
Quinto1
post Mar 30 2020, 10:16 AM
Post#1



Posts: 113
Joined: 23-April 16



Good Morning gentlemens, I hope to find you all in good health.
I need to separate drivers' license type, restriction and endorsement from a control field.
Codes: MNPSW

I used the wild card but it does not work, is there a way I can select only certain codes?
Expr1: IIf([F32]="*P*","P")

I will probably have to separate them into many fields and and then join the restriction and endorsement into their fields

Thanks
Quinto
Go to the top of the page
 
theDBguy
post Mar 30 2020, 10:18 AM
Post#2


UA Moderator
Posts: 78,094
Joined: 19-June 07
From: SunnySandyEggo


Hi Quinto. Not being familiar with your business process and without seeing your database, I have no idea what you're talking about. Can you give us more details and tell us what you mean by "codes?" Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Quinto1
post Mar 30 2020, 10:26 AM
Post#3



Posts: 113
Joined: 23-April 16




Thank you for asking
Codes are letters or numbers used by NYS Motor Vehicle Department and entered on the the driver's' license to show qualification and restrictions for drivers pertaining to type of vehicles and type of transportation the the driver can perform and also codes are used to identify drivers that might have restrictions such as eye glasses required or special equipment required to qualify to drive.
The data entry was done all in one field and rather that go about 1,200 records I would like to do it with some expression and that correct the table to accept the new format, and adjust all reports with the added field.

Go to the top of the page
 
Quinto1
post Mar 30 2020, 10:34 AM
Post#4



Posts: 113
Joined: 23-April 16



The codes listed are restrictions and endorsements:
M Motorcycle endorsement
N Restricted to class C vehicles
P Has passenger endorsement
S has school students transportation endorsement
W is a typo or wrong codes and not listed by DMV


Go to the top of the page
 
Jeff B.
post Mar 30 2020, 10:39 AM
Post#5


UtterAccess VIP
Posts: 10,479
Joined: 30-April 10
From: Pacific NorthWet


As <theDBGuy> points out, not knowing what your underlying database looks like (i.e., tables, fields) makes it difficult to suggest "how" to get data out of it.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
theDBguy
post Mar 30 2020, 10:57 AM
Post#6


UA Moderator
Posts: 78,094
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (Quinto1)
The codes listed are restrictions and endorsements:
M Motorcycle endorsement
N Restricted to class C vehicles
P Has passenger endorsement
S has school students transportation endorsement
W is a typo or wrong codes and not listed by DMV

So, all the codes are single characters? If so, will each position always have a code in it?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Larry Larsen
post Mar 30 2020, 11:01 AM
Post#7


UA Editor + Utterly Certified
Posts: 24,573
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi

If we were able extract the data what are you going to do with it..

You mentioned "accept the new format"..?

What is that..?

Are you thinking of splitting the code..?

Would help to show us before and after and if we are splitting into new field..?

Ahh.. so many questions..(sorry)
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
Larry Larsen
post Mar 30 2020, 11:04 AM
Post#8


UA Editor + Utterly Certified
Posts: 24,573
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi thedbGuy

When was the last time you looked at your licence .. iconfused.gif hat_tip.gif

Surly you guy's know what the codes represent..
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
theDBguy
post Mar 30 2020, 11:24 AM
Post#9


UA Moderator
Posts: 78,094
Joined: 19-June 07
From: SunnySandyEggo


Hi Larry. My license doesn't show the code but the text description. Maybe the codes are internal to the agency. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
projecttoday
post Mar 30 2020, 11:40 AM
Post#10


UtterAccess VIP
Posts: 12,168
Joined: 10-February 04
From: South Charleston, WV


You say you want to separate the code. So does that mean that it's part of the driver's license field as opposed to a separate field?
Possibly the Left or Mid function(s). You could use the LIKE operator but you would need a bunch of ORs. But you might preserve your index that way. You could also do a table-based approach.

--------------------
Robert Crouser
Go to the top of the page
 
Quinto1
post Mar 30 2020, 12:13 PM
Post#11



Posts: 113
Joined: 23-April 16



The type or class is a type of license issued by NYS DMV, There are multiple type of license the main one I am dealing with is the CDL type (Commercial Driver license) Class A with the right endorsement you can drive any vehicles, Class B reserved buses with capacity over 14 passenger and air brakes, Class C, CDL for passenger vehicles in public or school service with less than 14 passenger capacity and non CDL class c for straight trucks over 26,000 lb.
Each class has restrictions or endorsements that may apply only to one class or all. I have B restrictions and I must wear glasses when I drive, there are about 32 type restrictions and about 8 or 10 endorsements. Restriction range from eye wear to special equipment for people with disabilities. Endorsements qualifies the driver for special cargos such as apaangers, hazmat, flammables, etc.
I have attached 2 sample of my table, the full table has all the personal driver or employees informations.

The one in blue is the current and the other will be the edited or updated

I designed this over the years but this could not have been possible without your help.

Thank you

Quinto
This post has been edited by Quinto1: Mar 30 2020, 12:14 PM
Attached File(s)
Attached File  SampleCurrentDatabase.JPG ( 56.87K )Number of downloads: 7
Attached File  UpdatedTable.JPG ( 73.77K )Number of downloads: 6
 
Go to the top of the page
 
Gustav
post Mar 30 2020, 12:54 PM
Post#12


UtterAccess VIP
Posts: 2,237
Joined: 21-February 07
From: Copenhagen


Try with:

CODE
Select *, "p" As Code, From YourTable Where [F32] Like "*P*"

--------------------
Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
 
projecttoday
post Mar 30 2020, 01:23 PM
Post#13


UtterAccess VIP
Posts: 12,168
Joined: 10-February 04
From: South Charleston, WV


Okay, if you just want "p" then you would NOT need a bunch of ORs. Note that by default Access queries are not case-sensitive.

--------------------
Robert Crouser
Go to the top of the page
 
tina t
post Mar 30 2020, 03:01 PM
Post#14



Posts: 6,588
Joined: 11-November 10
From: SoCal, USA


@Larry
<Surly you guy's know what the codes represent..>
depends on which state issues the license. Calfornia's plain-vanilla license with no special endorsements or restrictions is Class C. in Delaware, it's Class D, Florida's is Class E, Connecticut's is Class 1, etc, etc.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
Quinto1
post Mar 30 2020, 03:31 PM
Post#15



Posts: 113
Joined: 23-April 16



I review the records and there are only a couple of different codes that I need to change so the select function will work well.
Thank you
Go to the top of the page
 
Quinto1
post Mar 30 2020, 03:35 PM
Post#16



Posts: 113
Joined: 23-April 16



Thank you Tina

I only need the New York version but I thought that when they started the CDL class years ago all states use the same letters for class and same codes for endorsement and restrictions

Quinto
Go to the top of the page
 
Larry Larsen
post Mar 31 2020, 04:10 AM
Post#17


UA Editor + Utterly Certified
Posts: 24,573
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi Tina

What a great informational site "DIGEST OF MOTOR LAWS"...

Now let me check my calendar and see if I can fit it in (good read), wow I have 14 day's left in "lock down"..

Different laws for different states can get really complicated.
Hope your keeping safe and well..
Larry
wavehi.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
tina t
post Mar 31 2020, 02:22 PM
Post#18



Posts: 6,588
Joined: 11-November 10
From: SoCal, USA


hi Larry, and you're welcome! smile.gif glad to hear you're staying well, and i'm fine, thanks. just still rolling my eyes about this mass hysteria, when the facts don't support it. below is a daily comparison between coronavirus and flu viruses. the website is interesting, comparing flu and coronavirus symptoms, treatment, etc.

direct from Johns-Hopkins website:

Infections

COVID-19: Approximately 800,049 cases worldwide; 164,610 cases in the U.S. as of Mar. 31, 2020.*

Flu: Estimated 1 billion cases worldwide; 9.3 million to 45 million cases in the U.S. per year.

Deaths

COVID-19: Approximately 38,714 deaths reported worldwide; 3,170 deaths in the U.S., as of Mar. 31, 2020.*

Flu: 291,000 to 646,000 deaths worldwide; 12,000 to 61,000 deaths in the U.S. per year.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th May 2020 - 07:00 PM