Converting Access to Access Project

[mjliscio]

Hello All,

I am working on a project that was started in access, but needs to be moved into an Access project so that It can be integrated with other forms. When I did this I realized that my database queries needed to be changed from Access to SQL code. for example instead of representing a date as #DATE# it needed to be represented as 'DATE'.

However I have one issue that I am having issues with.

I have a query that determines a few collumns by running information through a vb function and returning the result.

The issue is I do not know how to convert this code from Access to SQL so that it will work.

The code is

[CODE=SQL]SELECT Action_Indef([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER]),[LOG_NO]) AS [Action], TESTSTAT.*, Action_date([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER])) AS Action_Date_Fie ld, IIf([DUE]<[WAIVER],[WAIVER],[DUE]) AS Later
FROM TESTSTAT
WHERE [LOG_NO] LIKE '*'
ORDER BY Action_Indef([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER]),[LOG_NO]);[/CODE]

Can someone provide me with a sutible sql equvalent to this code

Thank You
Mike

[NeoPa]

Someone correct me if I'm wrong, but Access Projects typically can't use ADO recordsets and use ANSI-92 compatible SQL. Access MDBs typically use ANSI-89 compatibility and can use ADO. See ANSI Standards in String Comparisons for some related info.

[NeoPa]

I think your #DATE# v 'DATE' problem is a misunderstandin g on your part though. Dates are always denoted by hashes (#) in SQL. What you tried was a STRING equivalent (which can work - but is not comparing dates).

[Jim Doherty]

Hello All,

I am working on a project that was started in access, but needs to be moved into an Access project so that It can be integrated with other forms. When I did this I realized that my database queries needed to be changed from Access to SQL code. for example instead of representing a date as #DATE# it needed to be represented as 'DATE'.

However I have one issue that I am having issues with.

I have a query that determines a few collumns by running information through a vb function and returning the result.

The issue is I do not know how to convert this code from Access to SQL so that it will work.

The code is

[CODE=SQL]SELECT Action_Indef([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER]),[LOG_NO]) AS [Action], TESTSTAT.*, Action_date([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER])) AS Action_Date_Fie ld, IIf([DUE]<[WAIVER],[WAIVER],[DUE]) AS Later
FROM TESTSTAT
WHERE [LOG_NO] LIKE '*'
ORDER BY Action_Indef([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER]),[LOG_NO]);[/CODE]

Can someone provide me with a sutible sql equvalent to this code

Thank You
Mike

Access projects have SQL server backend based database tables and they can use ADO recordsets with standard 92... NEO is quite right in that hashes are out when working with dates and the quote mark ( ' ) is in as the replacement for that so to speak. There can be a large number of SQL conversion issues you might have to contend with overall not just those represneted by your current post.

I use and specialise in projects with SQL server most of the time now so I can feeeeeel for your conversion pain there particularly if your MDB is a huge complex thing... LOL!

I did come across a couple of freebie/shareware utilitites on the net when I first started using project eons ago (the names escape me at the moment) these can take some of the sting out of it conversion... but in all honesty ended up rewriting them myself anyway... so as to fit within the curtilage of SQL server ( which almost demands that you have a rethink on your data handling given the vast increase in power you have moving up one) for instance using UDF functions within a views or execution of complex stored procedures to manipulate data using temporary or table variables or using triggers etc.

Apart from the conversion syntax ie # for ' and % for * etc I can immediately see you are using the IIF function there. That is OUT the equivalent is the CASE statement in SQL server.

Tell you what... provided we keep this thread open and the result is eventually posted back to this thread for others to benefit from. I'm quite happy to hook up with you and give you advice as you go. As it is, I can see that there might well be some reorganising of your data you might want to address anyway given the optimum useage of SQL server it to keep processes on the server side as opposed to the application side.

You may even find that you could drop a few of the VB functions you might be using for data manipulation in favour of a stored procedure doing the work server side.when you consider the overall differences in Access and SQL Server.

In order to do this you will need to PM me with your email address to communicate...o ver to you

Regards

Jim

[NeoPa]

Access projects have SQL server backend based database tables and they can use ADO recordsets but use standard 92... NEO is quite right in that hashes are out when working with dates and the quote mark ( ' ) is in as the replacement for that so to speak.
...

Nice work Jim.
As to your quoted paragraph though, I'm afraid that was actually the reverse of what I was saying.
Dates should still be treated as dates and the hash (#) is still the correct delimiter for them (their literals). Quotes (') can be used but that is simply casting them to a string instead of doing Date comparisons. It can be done and some people find it easier, but it introduces some problems all of its own.
My recommendation would always be to understand and then use the date literals in preference.
Literal DateTimes and Their Delimiters (#). may help. It's written for a specifically Access audience, but I belive it's true for ANSI-92 SQL too (SQL Server; Access Projects; etc)

[Jim Doherty]

Nice work Jim.
As to your quoted paragraph though, I'm afraid that was actually the reverse of what I was saying.
Dates should still be treated as dates and the hash (#) is still the correct delimiter for them (their literals). Quotes (') can be used but that is simply casting them to a string instead of doing Date comparisons. It can be done and some people find it easier, but it introduces some problems all of its own.
My recommendation would always be to understand and then use the date literals in preference.
Literal DateTimes and Their Delimiters (#). may help. It's written for a specifically Access audience, but I belive it's true for ANSI-92 SQL too (SQL Server; Access Projects; etc)

I take your point NEO I was not thinking too directly in one my head on that one was more or less on the SQL Server side and for which the majority of script references I have seen and worked with externally or otherwise when using SQL Server use the single quote when delimiting a date so kind of you typically fall in line with it maybe because its easier and yes thats not always to say its the right way of course I accept that.

Sorry to have misrepresented you specifically

Jim

[NeoPa]

No worries Jim.
I liked your post - just that one point I wanted to clarify :)