Gerhardpet
asked on
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_TRANSACT IONS]![TRA N_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
This query will change to date format in Access which displays like this 9/18/2014
distdate: CVDate(Format([GL_TRANSACT
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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
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
You are saying that 20140928 is a string field.
Therefore, keep that formatting, and use this:
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))
Maybe you just need to use the convert to date function:
CDate(Format([GL_TRANSACTIONS]![TRAN_DATE],"yyyy-mm-dd"))
ASKER
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
I tried your query above and that converts it back to a date as 9/28/2014
ASKER
@Vitor: tried yours and I get #Num!
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")
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")
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")
ASKER
@Vitor still the same.
@Phillip your I get the wrong arguments error when doubling it
@Phillip your I get the wrong arguments error when doubling it
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Vitor now I get #Error
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?
Maybe you could import (as opposed to link) the database and post it?
ASKER
@Gustav
This works
This works
distdateText: Format(CVDate(Format([GL_TRANSACTIONS]![TRAN_DATE],"@@@@-@@-@@")), "yyyy\-mm\-dd")
ASKER
The first query does not work. I get #Num!