Need help with a few queries in Access

I have an Access database where I link to some table in Pervasive. In Pervasive I have date like this in string format 20140918

This query will change to date format in Access which displays like this 9/18/2014

distdate: CVDate(Format([GL_TRANSACTIONS]![TRAN_DATE],"@@@@-@@-@@"))

I need a query that will output the date like this 2014-09-18

Next query I need help with

I have a debit and credit column. In Access/Pervasive it is in 2 columns like this

Debit          Credit
10.00          0.00
0.00            10.00

Now I want a query that will combine these two columns and the Debit display as negative (-) like this taking the above example


The field names are

I'm importing the data to a PostgreSQL database
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
To the first query - would this work?


Open in new window

To the second query, how about this?

Select [CREDIT_AMT]-[DEBIT_AMT] as Amount

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GerhardpetAuthor Commented:
The second query works...that simple. I was doing plus (debit+credit) and it never dawned on me to use negative.

The first query does not work. I get #Num!
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It looks therefore like TRAN_DATE is actually already a date.

If that is the case, then in the query, press F4 to get the Property Sheet, and type yyyy-mm-dd in the "Format" line.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

GerhardpetAuthor Commented:
What I did now is format my original query to yyyy-mm-dd and that displays properly in Access

I need to export the data to csv and then the date exports as 9/28/2014

I need the date exported in the same format yyyy-mm-dd

When I format the original string field that has no effect. The date is still 20140928
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You are saying that 20140928 is a string field.

Therefore, keep that formatting, and use this:


Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
Maybe you just need to use the convert to date function:

Open in new window

GerhardpetAuthor Commented:
Yes in the database documentation is shows as a string but I know that when I use any date field in Crystal Reports it show as a data.

I tried your query above and that converts it back to a date as 9/28/2014
GerhardpetAuthor Commented:
@Vitor: tried yours and I get #Num!
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
If you are referring to Vitor's, then let's double it:


Open in new window

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
If you are referring to mine, then let's double that!

Format(CDate(Format([DateSerial(left([GL_TRANSACTIONS]![TRAN_DATE],4), mid([GL_TRANSACTIONS]![TRAN_DATE],5,2), mid([GL_TRANSACTIONS]![TRAN_DATE],7,2)),"yyyy-mm-dd")),"yyyy-mm-dd") 

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
I don't think double it is the solution. Maybe bring the Format out (sorry, I don't have Access here I can't test it before posting):

Open in new window

GerhardpetAuthor Commented:
@Vitor still the same.

@Phillip your I get the wrong arguments error when doubling it
Gustav BrockCIOCommented:
This is how to format your date:

    distdateText: Format(CVDate(Format([GL_TRANSACTIONS]![TRAN_DATE],"@@@@-@@-@@")), "yyyy\-mm\-dd")

or, going back to the source:

    distdateText: Format([GL_TRANSACTIONS]![TRAN_DATE],"@@@@-@@-@@")

The other is:


GerhardpetAuthor Commented:
@Vitor now I get #Error
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I'm getting to the stage where I can't offer any more advice without the database.

Maybe you could import (as opposed to link) the database and post it?
GerhardpetAuthor Commented:
This works
distdateText: Format(CVDate(Format([GL_TRANSACTIONS]![TRAN_DATE],"@@@@-@@-@@")), "yyyy\-mm\-dd")

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.