views:

58

answers:

3

i am having a problem with access sql SELECT statement. the problem is that when the backend is ACCESS-2007 it works; however when the backend is sql-server-2008, it is not returning anything. here is the statement:

SELECT IIf([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)] Like '*1.11*Other*','1.11 Other',[Lab Occurrence Form].[1 0 Preanalytical (Before Testing)]) AS [Occurrence Code], Count([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)]) AS [Count]
FROM [Lab Occurrence Form]
WHERE ((([Lab Occurrence Form].[Occurrence Date]) Between  Forms!Meeting_Reasons_Frequency!Text4  And  Forms!Meeting_Reasons_Frequency!Text2 )) And [Lab Occurrence Form].[1 0 Preanalytical (Before Testing)] Like '*1.*'
GROUP BY IIf([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)] Like '*1.11*Other*','1.11 Other',[Lab Occurrence Form].[1 0 Preanalytical (Before Testing)])
HAVING ((Count([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)]))<>0)
ORDER BY IIf([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)] Like '*1.11*Other*','1.11 Other',[Lab Occurrence Form].[1 0 Preanalytical (Before Testing)])

please note that what is not working is this: Forms!Meeting_Reasons_Frequency!Text4 and this Forms!Meeting_Reasons_Frequency!Text2 - when i replace those with constant values they work; however the trouble is that it they are not reading correctly what is in the textbox for some reason.

again: this query is working FINE with access as a backend, however when the backend switches to sql server it is not working! the reason again is because of the forms!text thing. does anyone know why?

update thanks to bob's suggestion i ran profile on sql-server and found something funny. it is indeed working correctly, however it is returning the date like this:

@P1 datetime,@P2 datetime','2010-04-30 00:00:00','2010-04-01 00:00:00'
A: 

This is a shot in the dark but I think it is your LIKE's. Access uses *'s as the wildcards while SQL Server uses %'s. Even if the syntax is translated to t-sql, I wonder if it also changes *'s to %'s. Try it out...

Mario
@mario: the statement is working on sql server no problem, the problem is that it does not take data from my text control on my form thats all
i am a girl
Hmm, well I would have to look at your form.
Mario
@jenny: @Mario's answer may be correct. If '*' is not replaced with '%', the the search pattern will be wrong and would return no results. The '*' will be interpreted literally (There must be an asterisk in the column value to match).
bobs
@bobs: once more: the query IS working on sql server. however the form!text is not being pulled out of there
i am a girl
@mario: how can i show u the form?
i am a girl
Access (Jet/ACE) indeed does supports the `%` wildcard character a) when in ANSI-92 Query Mode (see http://office.microsoft.com/en-us/access-help/about-ansi-sql-query-mode-mdb-HP003070483.aspx) b) when you use the `ALIKE` keyword rather than `LIKE`.
onedaywhen
+1  A: 

You can't just jam the controls into your string.

Between  Forms!Meeting_Reasons_Frequency!Text4  
 And  Forms!Meeting_Reasons_Frequency!Text2 

You need to separate them out and then look at your SQL string in the immediate window.

"<beginning query> Between " & Forms!Meeting_Reasons_Frequency!Text4 & _
" And " & Forms!Meeting_Reasons_Frequency!Text2 & " <rest of query>"

If you break on the creation of this string, you'll notice that the text box values will be displayed if you hover your mouse over the control name.

Jeff O
@jeff are you familiar with access sql?
i am a girl
@i am a girl - yes
Jeff O
+1  A: 

Try defining your form control references as parameters:

  PARAMETERS [Forms]![Meeting_Reasons_Frequency]![Text4] DateTime, 
     [Forms]![Meeting_Reasons_Frequency]![Text2] DateTime;
  SELECT IIf([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)] Like '*1.11*Other*','1.11 Other',[Lab Occurrence Form].[1 0 Preanalytical (Before Testing)]) AS [Occurrence Code], Count([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)]) AS [Count]
  FROM [Lab Occurrence Form]
  WHERE ((([Lab Occurrence Form].[Occurrence Date]) Between  Forms!Meeting_Reasons_Frequency!Text4  And  Forms!Meeting_Reasons_Frequency!Text2 )) And [Lab Occurrence Form].[1 0 Preanalytical (Before Testing)] Like '*1.*'
  GROUP BY IIf([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)] Like '*1.11*Other*','1.11 Other',[Lab Occurrence Form].[1 0 Preanalytical (Before Testing)])
  HAVING ((Count([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)]))<>0)
  ORDER BY IIf([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)] Like '*1.11*Other*','1.11 Other',[Lab Occurrence Form].[1 0 Preanalytical (Before Testing)])
David-W-Fenton