Query/SQL problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Query/SQL problem

Author

Topic

PBREP
Starting Member

9 Posts

Posted - 2003-12-16 : 13:47:26

Hello All:

PROBLEM: I'm attempting to generate a query without asking the user (ref. Expression prompt); like a stored procedure. Run query, I get no data...

Thanks,
~PM

(criteris) If Disposition = "D" And Validation = "Y"
Print/show me all:
- System Name
- Version
- LastName
- Location
- Validation Date
- Validation Required

&&SQL code:

SELECT ISD.[System Name], ISD.Version, ISD.LastName, ISD.Location, ISD.Disposition, ISD.Location, ISD.[Validation Required], ISD.[Validation Date]
FROM ISD
WHERE (((ISD.Disposition)="D") AND ((ISD.Location)="Stamford") AND ((ISD.[Validation Required])="Y"));

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-16 : 14:00:20

Not quite sure what you're asking, an example might make this easier to figure out, but check out this for information on creating conditional WHERE clauses:

http://weblogs.sqlteam.com/jeffs/posts/513.aspx

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-16 : 14:01:16

If you want Disposition = "D" And Validation = "Y", then why do you also have ISD.Location="Stamford"?

Run your query without the WHERE statement to see the data, then one by one add in your WHERE criteria.

Tara

PBREP
Starting Member

9 Posts

Posted - 2003-12-16 : 14:07:27

quote:
Originally posted by tduggan

If you want Disposition = "D" And Validation = "Y", then why do you also have ISD.Location="Stamford"?

Run your query without the WHERE statement to see the data, then one by one add in your WHERE criteria.

Tara

Hi Tara:

The reason for ISD.Location="Stamford" is an additional criterial for this specific report. For instance, for all locations in "Stamford" that are equal to "Y" (Validation) and equal to "D" (Disposition) I want to show. I maybe doing this incorrectly hence, reason for post.

Thanks for respondind.

~PM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-16 : 14:12:09

It doesn't look incorrect, so we need to see an example of your data and what you expect the result set to look like.

Tara

PBREP
Starting Member

9 Posts

Posted - 2003-12-16 : 14:27:21

quote:
Originally posted by tduggan

It doesn't look incorrect, so we need to see an example of your data and what you expect the result set to look like.

Tara

Hi Tara:

I'm not sure how I can show you data w/out a way (from) to attached a file. At any rate, the following below is what I need to see (a single record example just for space); I abreviated the Column names for space purposes.

Thanks,
~PM

(Column) Sys Loc Ver ValReq ValDate Dis LastName
(Data) Access Stamford 6.0 Y 12/16/03 D Johnson

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-12-16 : 14:28:17

Do what Tara said to do earlier:

WHERE (ISD.Location)="Stamford";

then

WHERE (ISD.Disposition)="D" AND (ISD.Location)="Stamford"

then

WHERE (ISD.Location)="Stamford" AND (ISD.[Validation Required])="Y";

and see which returns results and which do not.

PBREP
Starting Member

9 Posts

Posted - 2003-12-16 : 14:44:01

quote:
Originally posted by drymchaser

Do what Tara said to do earlier:

WHERE (ISD.Location)="Stamford";

then

WHERE (ISD.Disposition)="D" AND (ISD.Location)="Stamford"

then

WHERE (ISD.Location)="Stamford" AND (ISD.[Validation Required])="Y";

and see which returns results and which do not.

WHERE (ISD.Location)="Stamford"; (I receive all records for Stamford)
WHERE (ISD.Disposition)="D" AND (ISD.Location)="Stamford"
(I receive all records that have "D" & "Stamford")
WHERE (ISD.Location)="Stamford" AND (ISD.[Validation Required])="Y";(Nothing)

Thanks,
~PM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-16 : 14:46:00

Then you don't have any rows that match that criteria.

Run this to see what you have:

WHERE (ISD.[Validation Required])="Y"

Tara

PBREP
Starting Member

9 Posts

Posted - 2003-12-16 : 15:07:06

quote:
Originally posted by tduggan

Then you don't have any rows that match that criteria.

Run this to see what you have:

WHERE (ISD.[Validation Required])="Y"

Tara

Hi Tara:

I get nothing however, there are Y in my Validation Required column; I realize this is a very simple query, is there a bug in running MS Access 2K query?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-16 : 16:06:03

Are there any spaces in the column, meaning is the value Y<space> or something like that? No this is not a bug, it has to do with your data.

Does LIKE work in Access?

WHERE [Validation Required] LIKE '%Y%'

If you've got Y in that column, the above will return it. So if you get rows back now, it means that there is more data in that column than just Y.

Tara

PBREP
Starting Member

9 Posts

Posted - 2003-12-16 : 16:19:41

quote:
Originally posted by tduggan

Are there any spaces in the column, meaning is the value Y<space> or something like that? No this is not a bug, it has to do with your data.

Does LIKE work in Access?

WHERE [Validation Required] LIKE '%Y%'

If you've got Y in that column, the above will return it. So if you get rows back now, it means that there is more data in that column than just Y.

Tara

Hi Tara:

You have awesome troubleshooting / testing skills; Guess what, still not data when I tried:
WHERE [Validation Required] LIKE '%Y%'

I have just "Y" (Y)in my column.

~PM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-16 : 16:26:07

It turns out % is not the correct wildcard character to use for Access. I don't know Access, so I'm trying to help you out with my SQL Server skills. This link shows * for Access:

http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/2_005_81.htm

So change the query to:

WHERE [Validation Required] LIKE "*Y*"

Tara

PBREP
Starting Member

9 Posts

Posted - 2003-12-16 : 16:36:07

quote:
Originally posted by tduggan

It turns out % is not the correct wildcard character to use for Access. I don't know Access, so I'm trying to help you out with my SQL Server skills. This link shows * for Access:

http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/2_005_81.htm

So change the query to:

WHERE [Validation Required] LIKE "*Y*"

Tara

OK, now we have data, so what does this mean..?

- We know my data format is correct; hell should I just delete this field and recreate it?

~PM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-16 : 16:40:33

Your data is not correct, that's the point of this. You probably have a hard return or a space in that column. Deleting the column and recreating it won't help as whatever application is writing to it is going to do this again.

You need to figure out where in your application this is being caused, fix that part, then fix the data.

Tara

PBREP
Starting Member

9 Posts

Posted - 2003-12-16 : 16:46:45

quote:
Originally posted by PBREP

quote:
Originally posted by tduggan

It turns out % is not the correct wildcard character to use for Access. I don't know Access, so I'm trying to help you out with my SQL Server skills. This link shows * for Access:

http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/2_005_81.htm

So change the query to:

WHERE [Validation Required] LIKE "*Y*"

Tara

OK, now we have data, so what does this mean..?

- We know my data format is correct; hell should I just delete this field and recreate it?

~PM

Like I thought it's a bug that probally happens 1 out of 100000 (or probaly a corrupted field not visible to the eye)

The code I originally attempt to re-generate query worked. Gee I thought I was going nuts.

Thank you all and especialy Tara for here patience; Learned some good T-Tips

SELECT ISD.[System Name], ISD.Version, ISD.LastName, ISD.Location, ISD.Disposition, ISD.Location, ISD.[Validation Required], ISD.[Validation Date]
FROM ISD
WHERE (((ISD.Disposition)="D") AND ((ISD.Location)="Stamford") AND ((ISD.[Validation Required])="Y"));

PBREP
Starting Member

9 Posts

Posted - 2003-12-16 : 16:51:12

Delete and recreated the field (Validation Required)was the resolution.

~PM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources