Query-look at first 5 characters only

  • Thread starter Thread starter Guest
  • Start date Start date
I am confused. (sorry it has been a while and I am trying to get back in the
Access game!)
The field name is InvoiceIDByStore and the fields look like BA18AIN3952 or
BA18BIN3998. I want it to read only the BA18A or BA18B. With your answer
listed below do I type, LEFT([InvoiceIDByStore],5) ? I tried it and it
didn't work.

Dave Patrick said:
Left([field1],5)

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

HeyTriciaC said:
How do I put in the criteria to only look at the first 5 characters?
Click to expand...
Click to expand...
 
Try this.

SELECT *
FROM table1
WHERE (((Left([InvoiceIDByStore],5))='BA18A'));

(Change 'table1' to whatever your actual table name)

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
 
Dave said:
Try this.

SELECT *
FROM table1
WHERE (((Left([InvoiceIDByStore],5))='BA18A'));

(Change 'table1' to whatever your actual table name)
Click to expand...

Actually, if the field is indexed this would be more efficient...

SELECT *
FROM table1
WHERE [InvoiceIDByStore] Like "BA18A*";
 
I have a new question, can't post as new, it's similar to this one, but a
little different.

I have a colume of numbers, each is:*** - *** - ****. I want to do a make
table and delete the first two digits from the rest and having a colume only
has the later 8 digits: * - *** - ****.
How do I do that?

Thanks
 
Something like this should work.

SELECT Mid([Table1]![field1],3,10) AS TrimmedCol INTO table2
FROM Table1;

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
 
how do I also make the rest of the columes in table 1 appear in the make table?

Dave Patrick said:
Something like this should work.

SELECT Mid([Table1]![field1],3,10) AS TrimmedCol INTO table2
FROM Table1;

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

Lisa said:
I have a new question, can't post as new, it's similar to this one, but a
little different.

I have a colume of numbers, each is:*** - *** - ****. I want to do a make
table and delete the first two digits from the rest and having a colume
only
has the later 8 digits: * - *** - ****.
How do I do that?

Thanks
Click to expand...
Click to expand...
 
Something like;

SELECT Mid([Table1]![field1],3,10) AS TrimmedCol, Table1.field2,
Table1.field3, Table1.field4 INTO table2
FROM Table1;

When you're in the query design grid you can select any columns you wish
from table1

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
 
Thanks! it worked!

Dave Patrick said:
Something like;

SELECT Mid([Table1]![field1],3,10) AS TrimmedCol, Table1.field2,
Table1.field3, Table1.field4 INTO table2
FROM Table1;

When you're in the query design grid you can select any columns you wish
from table1

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

Lisa said:
how do I also make the rest of the columes in table 1 appear in the make
table?
Click to expand...
Click to expand...
 
Back
Top