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.

 

[Pat Hartman]

What is the equivalent Access SQL?

1. you don't need to convert all your queries to T-SQL. You may not need to convert any.
2. You don't need to convert to unbound forms
3. For those queries you do need to convert, you need to learn T-SQL rather than asking us to write SQL for a database we don't have.

The MOST IMPORTANT change you need to make when converting from Jet/ACE to SQL Server et al is to bind your forms to queries with criteria. Depending on the complexity of the search criteria, I either use 2 or three textboxes or combos on the form or I create a separate form with all the criteria options. The form builds the where clause based on the options selected. It then runs a count query. If the result is 1, it opens the edit form directly. Otherwise it opens a list form and the user has to select the record he wants to edit. Do NOT bind your forms to tables or queries with no criteria. This causes unnecessary network traffic and is very slow if your recordsets are large.

There may even be an SQL converter out there if you look for one.

 

[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 - AccessUserGroups.org

Convert your Access/Jet SQL queries into T-SQL to run on Microsoft SQL Server. It supports converting the most commonly used functions.

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.

 

[Pat Hartman]

It's already T-SQL Pat

Actually, I read the question wrong. He is going from T-SQL to ACE SQL. I took a stab at the conversion.
One difference to note is that when you use an expression to change the value of a field, you must change it's name. So I made up names for the three converted fields. I don't generally writ T-SQL so I'm not sure what the argument 101 is. I assumed by the field name that it was currency but if that isn't the case, you may need to use a different function. Here is a list of VBA functions that you might want to keep handy.

Access Functions (by category) - Microsoft Support

Find Help with Access functions in this categorical list.

support.microsoft.com

Another difference is that you can't create a field and then reference that field by the new name later. So, the current date function is Date(). Given that you are using the value to find records later, you cannot include time or you won't get the matches you expect.

Also, I don't understand the subquery. If the system_date is a calculated field, how are you using it as criteria in the where clause? I would expected the Max() to refer to some existing date rather than a calculated date.

I only included the parts that have to change.

IIf(Country & "" = "","US") as CountryNew,
IIf(Currency & "" = "", "USD") As CurrencyNew,
Format(smktval,Currency) as smktval as smktvalNew, sd_qty, box_qty, memo_seg
From dbo_StkRec_History
Where (Date() =
                             (SELECT        MAX(Date()) AS Expr1
                               FROM            _StkRec_History  ))