Convert to Access SQL

[prasadgov]

Hi,

I inherited a Access database and I need help to convert a SQL connection.
What is the equivalent Access SQL?

SELECT   CONVERT(VARCHAR(8), GETDATE(),112)  AS     system_date, acct, acct_name, symbol, sym_no, hold_type, sec_name, asset_type,
 sec_type, cusip, sedol, isin,
 --country,
 --Currency,
  Case when country = '' then 'US' ELSE country end  country, 
  Case when currency = '' then 'USD' ELSE Currency end  Currency,
  mult, avail_to_loan, price,
                      convert(float,smktval,101) as smktval , sd_qty, box_qty, memo_seg, chilled--,CONVERT(VARCHAR(19), GETDATE(),120) AS DATE
FROM            [xx.xxx.xx.xx].[DB PD].dbo.StkRec_History 
WHERE        (system_date =
                             (SELECT        MAX(system_date) AS Expr1
                               FROM            [xx.xxx.xx.xx].[DB PD].dbo.StkRec_History  ))

TIA

 

[pbaldy]

You can use the Format() function for the date, IIf() for the CASE, and maybe CDbl() for the other convert.

 

[GPGeorge]

The OP wants T-SQL converted to Access SQL....

 

[pbaldy]

you don't need to convert all your queries to T-SQL. You may not need to convert any.

It's already T-SQL Pat.

 

[isladogs]

There is an excellent Access to T-SQL converter at

SQL Converter - Access User Groups

accessusergroups.org

Unfortunately, whilst it does do a conversion to VBA, it doesn't convert T-SQL to Access SQL

I also have an Access SQL To/From VBA converter add-in utility available at:

SQL to/from VBA Converter Add-In

This Access add-in is an updated version of my SQL to VBA And Back Again utility which makes it easy to convert query SQL to a VBA string (or vice versa). Additonal functionality includes creating example code from the VBA string and temp queries to test the SQL / VBA output.

www.isladogs.co.uk

Perhaps you can use both tools in turn to achieve your desired result

 

[CJ_London]

You can also use the switch function as an alternative to nested iifs when converting the case when

 

[cheekybuddha]

You can try:

SELECT  
  Date() AS system_date,
  acct,
  acct_name,
  symbol,
  sym_no,
  hold_type,
  sec_name,
  asset_type,
  sec_type,
  cusip,
  sedol,
  isin,
  IIF(country = '', 'US', country) AS country,
  IIf([Currency] = '', 'USD', [Currency]) AS [Currency],
  mult,
  avail_to_loan,
  price,
  Round(smktval, 6) AS smktval,
  sd_qty,
  box_qty,
  memo_seg,
  chilled
FROM dbo_StkRec_History
WHERE Format(Date(), "yyyymmdd") = (
  SELECT
    MAX(system_date) AS Expr1
  FROM dbo_StkRec_History
)

Notes:
1. store dates as dates and not strings (system_date)
2. Your original expression in T-SQL for smktval is incorrect - style 101 is not applicable to floats, and is treated as 0 (round to 6 dp)
3. Obviously I have just used table names as if they are already linked tables, or imported.