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

Amount
-10.00
10.00

The field names are
GL_TRANSACTIONS.DEBIT_AMT
GL_TRANSACTIONS.CREDIT_AMT

I'm importing the data to a PostgreSQL database
LVL 1
GerhardpetAsked:
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?

Format([GL_TRANSACTIONS]![TRAN_DATE],"yyyy-mm-dd")

Open in new window


To the second query, how about this?

Select [CREDIT_AMT]-[DEBIT_AMT] as Amount
From GL_TRANSACTIONS

Open in new window

0

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!
0
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You are saying that 20140928 is a string field.

Therefore, keep that formatting, and use this:

DateSerial(left([GL_TRANSACTIONS]![TRAN_DATE],4), mid([GL_TRANSACTIONS]![TRAN_DATE],5,2), mid([GL_TRANSACTIONS]![TRAN_DATE],7,2)) 

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Maybe you just need to use the convert to date function:
CDate(Format([GL_TRANSACTIONS]![TRAN_DATE],"yyyy-mm-dd"))

Open in new window

0
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
0
GerhardpetAuthor Commented:
@Vitor: tried yours and I get #Num!
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
If you are referring to Vitor's, then let's double it:

Format(CDate(Format([GL_TRANSACTIONS]![TRAN_DATE],"yyyy-mm-dd")),"yyyy-mm-dd")

Open in new window

0
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

0
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):
Format(CDate([GL_TRANSACTIONS]![TRAN_DATE]),"yyyy-mm-dd")

Open in new window

0
GerhardpetAuthor Commented:
@Vitor still the same.

@Phillip your I get the wrong arguments error when doubling it
0
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:

    AmountText: Format(GL_TRANSACTIONS.DEBIT_AMT- GL_TRANSACTIONS.CREDIT_AMT, "0.00")

/gustav
0
GerhardpetAuthor Commented:
@Vitor now I get #Error
0
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?
0
GerhardpetAuthor Commented:
@Gustav
This works
distdateText: Format(CVDate(Format([GL_TRANSACTIONS]![TRAN_DATE],"@@@@-@@-@@")), "yyyy\-mm\-dd")

Open in new window

0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.