can I remove characters in a query? 1

[pduncan]

I am writing an append query to copy selected data to another table - however I also need to remove some of the data that get's moved.
For example one field that gets moved is PO number - however in the destination table, I need to remove the first 5 digits.
Another example - several date fields are copied with the query - they are in MM/DD/YYYY format - but in the destination table, they need to be MM/DD/YY - any easy way to make these changes?

 

[dhookom]

You can use expressions in your append query like
NewPONum: Mid([PO Number],6)
Dates are dates are dates. Formatting is set in controls on forms. Are your "date" fields actually text fields?

Duane
MS Access MVP
[green]
Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]
Find out how to get great answers faq219-2884.[/green]

[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[lespaul]

You will need to use functions to get the data you want. This should point you in the right direction.

Select Right(OldPONumber, len(OldPONumber) - 5), Format('mm/dd/yy', OldDateField) FROM OldTable

Once you get the information correct in the SELECT query, use the INSERT command:

insert into newtable (PONumber, NewDateInfo) Select Right(OldPONumber, len(OldPONumber) - 5), Format('mm/dd/yy', OldDateField) FROM OldTable

Leslie

 

[lespaul]

Mid also works and Duane's right about the date stuff too!
Have a star!

Leslie

 

[pduncan]

Thanks for the quick answers!
Here is the Query I used:

SELECT Right(PONo,Len(PONo)-5) AS OldPO, Format('mm/dd/yy',[InvDate]) AS OldDate
FROM tblInvoice;

The returned values are correct for the PO but the date I get "mm/dd/yy" for every entry.

dhookom: I am using text for the date fields in the destination table currently (the source uses short date fields) - tried experimenting with several ways but could not come up with anything that worked.

 

[pduncan]

I figured it out...

SELECT Right(PONo,Len(PONo)-5) AS OldPO, Format([InvDate],"mm/dd/yy") AS OldDate
FROM tblInvoice;

thanks!

PDUNCAN - MEMPHIS, TN

When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.

 

[dhookom]

I hope you have a good reason to use a text field for you date in the destination table. I have never found this to be a good choice and would think it creates lots of issues.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[pduncan]

Duane, - nope no good reason at all - short of not knowing any better. BUT - when I change it to a date field, it adds "20" to my "04" - which is what I was trying to remove in the first place.
I have tried short/med dates with a variety of input masks - but I always get 2004 for year when it's a date field.

 

[PHV]

It's not an input mask problem but a display format.
Try to never mix storage and presentation problems.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244