Solved

sql statement in vb6

Posted on 2002-07-29
18
299 Views
Last Modified: 2012-05-04
hi
i am trying to do querie in vb but i am not getting any where with it

here goes
MySql = "SELECT * FROM Log WHERE ((Name ='Arthur') AND (LogDate=#20/06/2002#));"

this returns 0 records but if i do the select as follows
MySql = "SELECT * FROM Log WHERE ((Name ='Arthur'));"
it will find records with Arthur in them

and if i do the other half
MySql = "SELECT * FROM Log WHERE ((LogDate =#20/06/2002#));"
this returns records with 21/6/2002 in them
but when i put the 2 together with an and it does'nt work
i was told that you could copy the sql queurie out of access and just replace the " with ' but it doesnt work either
0
Comment
Question by:arthur_moody
  • 7
  • 4
  • 3
  • +4
18 Comments
 
LVL 17

Expert Comment

by:inthedark
ID: 7187203
I.H.O. you must always use braces arround all variables and file names. Beacause the syntax is different between Access and ADO (the reserved words are different). Also in this way you future proof your statemnents incase MS change the spec of the reserved words list.

Example:

SQL ="Select * from [log] Where [Name] = etc....

Sometimes you even get a not very helpfull error message.

Try it....it may help.


0
 

Author Comment

by:arthur_moody
ID: 7187207
hi
i just tried your suggestion for putting brackets around the feild name but i did'nt help
thanks
anyway
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 7187208
Are there any records in the table with *both* Arthur as name and Jun 20 for the date?  Because unless a record matches both criteria, it will not produce any results.

0
 

Author Comment

by:arthur_moody
ID: 7187211
hi Paul
yes there is a record in the database that meets both these requirements
thanks
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 7187223
Does the query work in Access then?
0
 
LVL 38

Accepted Solution

by:
PaulHews earned 100 total points
ID: 7187226
Format your date as follows:

MySql = "SELECT * FROM Log WHERE ((Name ='Arthur') AND (LogDate=#2002/06/20#));"

I suspect that the provider is not understanding the date the way you provided it.  yyyy/mm/dd will never be misunderstood.
0
 

Author Comment

by:arthur_moody
ID: 7187233
i have just tried your suggestion Paul with the date but it still does'nt work i also dont think its the date
formating because if i try the queurie just for the date MySql = "SELECT * FROM Log WHERE ((LogDate #20/06/2002#));"
it works and if i just querie for the name

MySql = "SELECT * FROM Log WHERE ((Name ='Arthur'));"
it works it just does'nt work when i querie for both
the querie works correctly in access
0
 
LVL 4

Expert Comment

by:zuijdhoek
ID: 7187236
Statement looks OK to me.
This may sound a little bit silly, but are you sure your recordset contains records which satisfy both criteria?
0
 
LVL 4

Expert Comment

by:zuijdhoek
ID: 7187240
Statement looks OK to me.
This may sound a little bit silly, but are you sure your recordset contains records which satisfy both criteria?
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:arthur_moody
ID: 7187243
hi  zuijdhoek
i have a access database with a table called log and it has only two fields #1 Name #2 LogDate
and ther is two recordes
the first record Name: Arthur LogDate: 20/06/2002
0
 
LVL 4

Expert Comment

by:zuijdhoek
ID: 7187261
Doesn't make sense to me. Are you sure field Date is Date/Time type? Are you sure the resultset is empty?
What happens if you try to loop through the resultset?
How do you retrieve the number of affected records?
How did you establish connection between VB and Access?

Mark
0
 
LVL 3

Expert Comment

by:sanjaykattimani
ID: 7187282
try
MySql = "SELECT * FROM Log WHERE ((Name ='Arthur') AND (LogDate=#06/20/2002#));"
0
 
LVL 4

Expert Comment

by:AlonHirsch
ID: 7187284
Hi,

It could be that the date field contains time information as well. This would mean that unless the date (and time) was 20/06/2002 12:00:00 it would not be picked up.

You can either use Format in your query :
MySql = "SELECT * FROM Log WHERE ((Name ='Arthur') AND (Format(LogDate,"dd/mm/yyyy")='20/06/2002'));"

or you can try passing the dates to the query as mm/dd/yyyy as Access likes them :
MySql = "SELECT * FROM Log WHERE ((Name ='Arthur') AND (LogDate=#06/20/2002#));"

HTH,
Alon
0
 

Author Comment

by:arthur_moody
ID: 7187295
hi everyone
i have just sorted the problem i was doin up a sample of my problem by copying out the relevant code form my project and pasting it into a new vb project to post for eveyone to look at then i ran it and it works so i then copied this into an older version of the project before i started adding the database stuff and it know works nothing was changed in the code so it beats me as to why it didnt work
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 7187377
If the SQL statement is the same, there is no why it didn't works! So the possibilty maybe is your database records is already incorrect previously so that no records are selected.

And personally think that a grade 'C' is not fair to PaulHews. And i agree the Paul's point that: "I suspect that the provider is not understanding the date the way you provided it.  yyyy/mm/dd will never be misunderstood"
0
 

Author Comment

by:arthur_moody
ID: 7187409
i did not change the code at all i copied and pasted the code from my project unmodified and pasted it into a new project same variables same database connection there was no typing or changing at all it is using the same sql with the same date format i DID'NT change anything.

i only gave a c because i did not know who to give the points to because i tried everything that was posted here but nothing worked so i had to give the points to someone and Paul submitted the best things to try it is not a reflection on what i thought of his programming skills.

if you are unhappy with this and would like to have a look at the original vb project(working and not working) let me know and i will post them
0
 

Author Comment

by:arthur_moody
ID: 7187412
i did not change the code at all i copied and pasted the code from my project unmodified and pasted it into a new project same variables same database connection there was no typing or changing at all it is using the same sql with the same date format i DID'NT change anything.

i only gave a c because i did not know who to give the points to because i tried everything that was posted here but nothing worked so i had to give the points to someone and Paul submitted the best things to try it is not a reflection on what i thought of his programming skills.

if you are unhappy with this and would like to have a look at the original vb project(working and not working) let me know and i will post them
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 7188527
Just for future reference, Arthur, you do have options if you find the solution yourself.  Just post a note to customer service to either delete or add the question to the PAQ.  Either way, your question points will be refunded.

If you are awarding the question, the grade is an issue for me and many experts.  Not just because it reflects on my ability (I think I have a pretty good record overall) but because it can be a sign of gratitude or contempt.  Most of us feel like a "C" grade is closer to contempt, especially when I didn't say I had "the answer," I was only making suggestions.

And by the way, if you format your dates as dd/mm/yyyy, it is quite possible you will get it wrong, because the tenth of june will appear as 10/06/2002 and the provider can interpret it as October 6, 2002 despite what your locale settings say.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now