DoCmd.OpenReport does not work with ASC in where condition

[PNC]

Hi,

This is my code

DoCmd.OpenReport stDocName, acPreview, , "ASC([Field]) like ASC("& Variable &")"

ERROR: Data type mismatch in criteria expression

It works fine in a query, therefore it should also work in the code, right?

Any ideas why not?

Thanks,
PNC.

 

[RoyVidar]

Works on my setup, when testing, well, I used something like the below (= operator on numerics):

[tt]DoCmd.OpenReport stDocName, acPreview, , "ASC([Field]) = " & ASC(Variable)[/tt]

But get that message, if the field in question is Null.

Roy-Vidar

 

[PNC]

When I try to add the Nz function, I get an error.

Variable = "' Var '"
Blank = "' N/A '"
DoCmd.OpenReport stDocName, acPreview, , "ASC(Nz([Field],"& Blank &")) like ASC("& Variable &")"

ERROR:
Invalid Procedure Call or Argument

PNC.

 

[ethorn10]

notice how roy put the & before the ASC(variable). try it that way with your NZ function as is.

 

[RoyVidar]

Since you are mixing items that is supposed to be resloved within the query and at run time, I'm not sure what's happening, but since you are passing "' var '" in one string and "' N/A '" in the other, it should give a match -> both should return 39 (the single quote, the Asc function returns the character code for the first character in a string)

But I don't understand how the SQL is supposed to work (but, then I don't understand much of SQL;-)). And again, I think one uses the Like operator on text with wildchards, but uses the equal sign on numerics.

Won't you rather try telling us what you are trying to achieve, perhaps with some sample data, and we'll se if we can help - and shouldn't this rather be in the query and sql forum?

Roy-Vidar

 

[PNC]

If the Asc function only return a value for the first character, it's useless to me...

I'm trying to do a case sensitive comparison... I didn't want to use the StrComp function because, I won't be able to use a wildcard with that function, I think....

I tried to dynamically alter my pass-through query, but I could not get my querydef to refresh with the new where clause in time for me to run the report for the first time, it works only after the first try... I don't know if it's because my main menu has an ODBC connection open to SQL server...

Don't know what to do anymore... I feel like smashing my computer!!!

PNC.

 

[RoyVidar]

....sample data, what you wish to achieve... though I'm quite useless on SQL, there are members here who can achieve rather amazing things...

I think, in a pass-through query, you could have done something like this

[tt]...where cast(Field as varbinary(30)) = cast('yourtext' as varbinary(30))[/tt]

Then - if a string with length 4, you should be able to use something like this:

[tt]...where cast(Field as varbinary(2)) = cast('yo' as varbinary(2))[/tt]

- which I think it should be possible to alter in a querydef?

Did you try:
[tt]currentdb.querydefs.refresh '?[/tt]

Roy-Vidar

 

[PNC]

I use the Collate keyword to change the character set to be case sensitive, it works like a charm, my problem is changing the query on the fly for the users who wants to use that option and the others who doesn't.


I tried CurrentDB.QueryDefs.Refresh but somehow when the reports runs for the first time, it still uses the orginal query...

I'm clueless to why...

Thanks for all your replies,
PNC.