Solved

Need help with a few queries in Access

Posted on 2014-10-10
16
323 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 50

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
 
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 50

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 50

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

696 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