Solved

Need help with a few queries in Access

Posted on 2014-10-10
16
304 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 46

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 46

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
HasData 9 35
running an update statement after a msgbox (MS Access) 3 27
Close Print Preview button not active 12 35
Track name AutoCorrect info 14 45
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

930 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

10 Experts available now in Live!

Get 1:1 Help Now