Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL VIEW

Posted on 2013-01-17
7
Medium Priority
?
281 Views
Last Modified: 2013-01-17
Hi,

Im not used to working with SQL view. I am trying to put the condition

"SC" into field DR/CR

The SQL is

INSERT INTO [A1 - APAC Data] ( SEC, [WINNAR NO], LEDGER, [DR/CR], CCY, AMOUNT, [VALUE], [AGE BY VALUE], ENTRY_DATE, SFIELD_7, SFIELD_8, SFIELD_9, TRAN_CODE, GBP_EQUIV, [Root Cause] )
SELECT [1 - Import APAC].SEC, [1 - Import APAC].[WINNAR NO#], [1 - Import APAC].LEDGER, [1 - Import APAC].[DR/CR], [1 - Import APAC].CCY, [1 - Import APAC].AMOUNT, [1 - Import APAC].VALUE, Val(Replace([1 - Import APAC]![AGE BY VALUE]," days","",1)) AS Expr1, [1 - Import APAC].ENTRY_DATE, [1 - Import APAC].SFIELD_7, [1 - Import APAC].SFIELD_8, [1 - Import APAC].SFIELD_9, [1 - Import APAC].TRAN_CODE, [1 - Import APAC].GBP_EQUIV, [1 - Import APAC].[Root Cause]
FROM [1 - Import APAC];


Can anyone amend the code to include that condition?

Thanks
0
Comment
Question by:Seamus2626
[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
  • 3
  • 3
7 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 38787452
INSERT INTO [A1 - APAC Data] ( SEC, [WINNAR NO], LEDGER, [DR/CR], CCY, AMOUNT, [VALUE], [AGE BY VALUE], ENTRY_DATE, SFIELD_7, SFIELD_8, SFIELD_9, TRAN_CODE, GBP_EQUIV, [Root Cause] )
SELECT [1 - Import APAC].SEC, [1 - Import APAC].[WINNAR NO#], [1 - Import APAC].LEDGER, [1 - Import APAC].[DR/CR], [1 - Import APAC].CCY, [1 - Import APAC].AMOUNT, [1 - Import APAC].VALUE, Val(Replace([1 - Import APAC]![AGE BY VALUE]," days","",1)) AS Expr1, [1 - Import APAC].ENTRY_DATE, [1 - Import APAC].SFIELD_7, [1 - Import APAC].SFIELD_8, [1 - Import APAC].SFIELD_9, [1 - Import APAC].TRAN_CODE, [1 - Import APAC].GBP_EQUIV, [1 - Import APAC].[Root Cause]
FROM [1 - Import APAC]  WHERE [DR/CR] = "SC"

Open in new window

0
 

Author Closing Comment

by:Seamus2626
ID: 38787464
Thanks mbizup!

Seamus
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38787488
Glad to help!  

Cleaning the syntax a little:

INSERT INTO [A1 - APAC Data] ( SEC, [WINNAR NO], LEDGER, [DR/CR], CCY, AMOUNT, [VALUE], [AGE BY VALUE], ENTRY_DATE, SFIELD_7, SFIELD_8, SFIELD_9, TRAN_CODE, GBP_EQUIV, [Root Cause] )
SELECT Imp.SEC,  Imp.WINNAR NO#],  Imp.LEDGER, Imp.DR/CR],  Imp.CCY,  Imp.AMOUNT, Imp.VALUE, Val(Replace([1 -  Imp![AGE BY VALUE]," days","",1)) AS Expr1,  Imp.ENTRY_DATE,  Imp.SFIELD_7, Imp.SFIELD_8,  Imp.SFIELD_9,  Imp.TRAN_CODE, Imp.GBP_EQUIV,  Imp.[Root Cause]
FROM [1 - Import APAC] Imp WHERE  Imp.[DR/CR] = "SC"

Open in new window


Ideally all those field and table names should be changed to not include spaces or special characters.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Seamus2626
ID: 38787495
Why is that out of interest?

Thanks
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38787779
See this:
http://support.microsoft.com/kb/826763
Ideally, MS Access would catch all of these when naming fields, but it does not.  And, use of some of these 'reserved'  characters can be allowed by use of brackets. But they may not work in other databases and code, such as vba.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38787824
Sorry I missed your comment -  I got the email notif for Jerry's comment though.  :-)

Anyhow, just seconding what he said...

Not just for table and field names... but for variables, objects, etc.

Reserved words, spaces and special characters can cause real problems in code and queries.  Not only does your code etc look cleaner when you steer clear of them, but you can omit those brackets, and count it as one less thing to go wrong.

Most of the regulars here use the Reddick VBA (or similar) conventions for naming objects, fields.  Standards specific to Access are midway down the page:
http://www.xoc.net/standards/rvbanc.asp
0
 

Author Comment

by:Seamus2626
ID: 38787847
Okay, i see.

Thanks for the heads up guys
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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

618 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