Seamus2626
asked on
SQL VIEW
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad to help!
Cleaning the syntax a little:
Ideally all those field and table names should be changed to not include spaces or special characters.
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"
Ideally all those field and table names should be changed to not include spaces or special characters.
ASKER
Why is that out of interest?
Thanks
Thanks
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.
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.
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
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
ASKER
Okay, i see.
Thanks for the heads up guys
Thanks for the heads up guys
ASKER
Seamus