VB commands in SQL statements 3

[Kalechibki]

As part of a query, I need to be able to select only part of a field for a certain record to see if it fits in with a query (I'm working with a database someone else created, and the powers that be are used to the layout so they don't want me to change it).

So, the only way that I know how to do the query would be the following, which I've got to work in a stand alone query:
SELECT Count([SurveyResults].[Ques1]) As Expr1
FROM SurveyResults
WHERE ((InStr(1,[Ques1],'Colleague',1)>0));

However, when I try to flip over into Visual Basic to do the same (because of the sheer volume of queries, I feel that it would be impractical to try and leave all of them as stand alone queries to be called), using:

DoCmd.RunSQL ("SELECT Count([SurveyResults].[Ques1]) FROM SurveyResults WHERE ((InStr(1,[Ques1],'Colleague',1)>0));&quot

the query doesn't work! Anyone have any insight into why it wouldn't work? I'm using Access 2000 and VB 6.0

Brian

 

[scriverb]

The command:

DoCmd.RunSQL will only run ACTION SQL(Append, Update, Delete, etc.) It will not execute a Select query.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

Just what is the problem? You need to run a number of queries and pick off the counts for displaying in a form?

You use the DLookup with a SQL statement to retrieve the value from the above query if needed.
DLookup("[Expr1]", "SELECT Count([SurveyResults].[Ques1]) As Expr1
FROM SurveyResults
WHERE ((InStr(1,[Ques1],'Colleague',1)>0));&quot

The above function will return the Count value that is returned as column/field Expr1 from the SQL statement. This value can then be assied in VBA code to a control or whatever.

Is this what you are looking for?


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Kalechibki]

THANKS SO MUCH!!!!!!!!!!

 

[ejmiller2]

thanks scriverb, you seem to have all of the answers im looking for. From now on instead of searching for a keyword of my problem..im just going to search through all of your posts! thanks!

eric

 

[scriverb]

Thanks for the Stars guys/gals. Much appreciated. As for me knowing everything, Don't believe it. I just have an overactive curiosity to figure things out. I can answer these questions because at somepoint in the past I screwed up somecode and couldn't get it to work. Then like "a blind squirrel finding a nut every once in a while" I figured out the answer mostly due to trial and error.

Sites like this have been a God send for me through my learning ACCESS as my department had no one else to work with or even talk about ACCESS issues and problems. I kept two PC's active on my desktop. One to work on current projects and one to monitor Programmer Coolaboration sites like Tek-Tips.

Good luck on your projects.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[ejmiller2]

scriverb..

one question...wonder if you ever ran past this...

The microsoft jet database engine cannot find the input table or query 'SELECT [Employee]. AS email FROM Employee'. Make sure it exists and that its name is spelled correctly

not sure why i get this...i know employee is a table that exists and that email is a field in that table...maybe im doing something wrong...below is my code...

TTo = DLookup("[email]", "SELECT [Employee].[Email] AS email FROM Employee&quot

thanks again!...

eric

 

[scriverb]

There are three parameters to the DLookup function. You are only using the first two. The second parameter is the name of the table only surrounded by quotes. The third parameter is the expression to select the record. See the following code:

TTo = DLookup("", "Employee", [COLOR=red] this is where you put a string expression together to select a single record [COLOR=blue])[COLOR=black]

Here is an example of the statement with an expression. Since I don't know the name of your fields I will just make some up:[COLOR=blue]
TTo = DLookup("[email]", "Employee", "[CriteriaFieldName] = 'Jerry'&quot[COLOR=black]

The expression building is the tricky part of the DLookup. Text fields have to be compared to values in single quotes. Numbers can be compared directly. Dates are compared to date string surrounded by # signs.

Post back if you have any problems with this.



Bob Scriver
[COLOR=blue]Want the best answers? See FAQ181-2886
[I][COLOR=green]Nobody believes the official spokesman... but everybody trusts an unidentified source.[/I][COLOR=red]
Author, [b]Bagdad Bob???[/b][COLOR=black]

 

[ejmiller2]

once again...you did it...thanks!! i guess as long as youre on a roll...got another one for ya...way off the subject..

i am outputting a report to rtf format. the only problem is only the text shows up. In the report i have shaded areas that need to be there. i have also tried html format...but it also looses the shading here...any suggustions?..thanks again!

eric

 

[scriverb]

Try using the .snp or Snapshot View report format to export your reports. I believe it comes standard with A2k and if you have 97 and need the install file I can provide that. I looked at the MS download page and they are now longer providing the file for downloading.

Send me an email(see my profile) and I will return a copy to you.

The only problem with SnapShot (.snp) is that anyone wanting to view the report will have to have it installed also on their PC.

Other than that all of the special formating features of an ACCESS report are maintained and intact.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[ejmiller2]

scriverb....once again you have the answer...

actually, i think im going to pdf file now..hopefully that will work a little smoother..didnt reallly like thye snp file. anyway, i saw code to put in access as a module and then it seems easy to output as a pdf...

i asked this question else where, but thought maybe you proly have the answer....what would be wrong with changing the default printer in code to the pdf writer, then printing the report?...wouldnt that do the same thing?..

thanks agian!

eric

 

[scriverb]

I believe that sounds like a plan. When you get it coded and working post it back here so we may all see you work.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[ejmiller2]

hmm...ok, been trying, this will take a while... one last question before i dig into this hard...do you have any idea if the pdf file will be larger than the snp file?..or how i can find out?..thanks
eric

 

[ejmiller2]

ive noticed that visual basic has a printer object and that it is more easily manipulatable...why doesnt access have this?...how do i import this object into acces?...

eric

 

[scriverb]

Here is a thread that may be helpful: thread705-581008

Let me know if this helps.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[arimatan]

Thanks scriverb,
your tip with DLookup was very helpful for me, too.

thanks again

 

[DHNuno]

I have a problem with version 1.2 of the Squirrel. The POS blocks with much frequency when I make some type of operation, like when I give payment. When I go to event log in Point of sale control appears this message. Error: table error in department 2 (Restaurant) table 2 (2).

I think the POS blocks because it loses the communication with squirrel server .
To initiate again the pos system, it's necessary to activate again the services.

When I go the POS/TERMINAL,Point of sale control. Where it appears the message (host is alive), it appears (sending failure).

The squirrel icone in the tools bar close to the hours loses the net points.

Somebody knows what's happen here? help me please.

Thanks