Solved

Need help with a few queries in Access

Posted on 2014-10-10
16
299 Views
Last Modified: 2014-10-10
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
0
Comment
Question by:Gerhardpet
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 250 total points
ID: 40372819
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
 
LVL 1

Author Comment

by:Gerhardpet
ID: 40372850
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40372859
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
 
LVL 1

Author Comment

by:Gerhardpet
ID: 40372884
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40372905
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40372924
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
 
LVL 1

Author Comment

by:Gerhardpet
ID: 40372931
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
 
LVL 1

Author Comment

by:Gerhardpet
ID: 40372934
@Vitor: tried yours and I get #Num!
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40372936
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40372939
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40372943
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
 
LVL 1

Author Comment

by:Gerhardpet
ID: 40372947
@Vitor still the same.

@Phillip your I get the wrong arguments error when doubling it
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 40372949
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
 
LVL 1

Author Comment

by:Gerhardpet
ID: 40372951
@Vitor now I get #Error
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40372952
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
 
LVL 1

Author Comment

by:Gerhardpet
ID: 40372992
@Gustav
This works
distdateText: Format(CVDate(Format([GL_TRANSACTIONS]![TRAN_DATE],"@@@@-@@-@@")), "yyyy\-mm\-dd")

Open in new window

0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now