Querying using like statement

[NLillith]

I am attempting to write a query in SQL for the backend of a MS Access Database. I have several tables set up and a few forms which will use this query to pull and propogate data.

SELECT Tapes.[Tape #], Tapes.Status, Tapes.System, Tapes.[Backup Date], Tapes.[Backup Date Text], Tapes.[Backup Desc], [Type of Backup].[Type of Backup]
FROM [Type of Backup], Tapes;
WHERE Tapes.[Tape #] = LIKE "**";

The parts that are underlined is where I am having trouble. In these particular tables, specifically in Tapes table, I have tapes with leading zeros.

They look like close to this: 000001.

What I want to be able to accomplish with this query is to be able to put into the input box any number say 14 and then pull all the records that have the number 14 in them. The rest of the data will be pulled with them as well, but all of our tapes have leading zeros. Tapes from 000001-000130.

So I'm hoping someone can help. I've been trying to figure this out for a little over two weeks and have had no such luck. Any ideas would be most helpful.

Also, I get a syntax error with the last line saying "characters found after end of SQL statement".

 

[jdraw]

The semicolon after Tapes is the first issue - remove this semi colon.
Most people will tell you to use a naming convention that does not include spaces or special characters in column and object names. What you have requires the use of "[" and "]" and will be a source of problems in future.

You might want to look at this tutorial.
http://www.fontstuff.com/access/acctut01.htm

 

[NLillith]

jdraw - I did remove the semicolon after I posted that and realised that could have been the issue, however it seems to be a problem with the like statement rather than a semicolon issue now.
"Syntax error (missing operator) in query expression 'Tapes.[Tape #] = LIKE "**"".

What I want it to do is query without the leading zeros so that someone can just put in the number 14 and it will pull all records with the tapes that have the number 14 in them.

Also, I was given this database pre-built with the special characters in the name and that is the way this person wants it. We are eventually going to port this over to an SQL database.

Edit: I used that link you had put in there and it works great except it doesn't do what I want it to do which ignore the leading zeros on my tape numbers. Also, putting the tape numbers in without the leading zeros is not an option.

 

[Brianwarnock]

I would have thought that your where statement would be like
Where tapes.[tape#] like "*" & [prompt] & "*"

Brian

 

[NLillith]

Brian - that is similar to what it looks like in the back end now.

However, I still need to be able to query all the tape numbers with ignoring the leading zeros.

Tapes look like this:

000001 or 000130 and everything in between.

 

[Brianwarnock]

Then I don't understand why it doesn't work.

You use the terms like and similar or other vagueries

They look like close to this: 000001

.
post the code and an exact field content with datatype.

What I posted finds
000014
014000

whether the field is text or custom number format, I just enter 14 in the prompt.

Brian

 

[NLillith]

Brian,

That worked! Thank you! I had the syntax wrong so it wasn't calling it properly but it worked. Thank you so much.

Edit:

Hmm, interesting. It works, but now one of the populated fields from the input information is not working properly.

We have a table specific for "Type of Backup" IE: the person puts in whether it was a weekly, monthly, or end of the year and when it calls the tape number, it populates it with all three of those selections even if the person selected Weekly or if they select Monthly, or End of the year.

Not sure what the trouble is there.

Orginal:

SELECT Tapes.[Tape #], Tapes.Status, Tapes.System, Tapes.[Backup Date], Tapes.[Backup Date Text], Tapes.[Backup Desc], [Type of Backup].[Type of Backup]
FROM [Type of Backup], Tapes
WHERE ((Tapes.[Tape #] like "*" & [Enter Tape Number] & "*"));

Modified:

SELECT Tapes.[Tape #], Tapes.Status, Tapes.System, Tapes.[Backup Date], [Type of Backup].[Type of Backup]
FROM [Type of Backup], Tapes
WHERE ((Tapes.[Tape #] like "*" & [Enter Tape Number] & "*"));

 

[Brianwarnock]

Phew!! I'm glad that's sorted thanks for coming back.

Brian

 

[NLillith]

Got the problem sorted. Thanks all for the help. Much appreciated.

 

[NLillith]

Now here's a new one.

The query returns three items even though there's only one in the table.

 

[Brianwarnock]

This looks like the result of a cartesian join where you get
number of records returned = number in table1 * number in table2

This happens when there is no control by way of a Join and/or Where clause.
You have no Where clause restricting the type of backup, however I am still a little unclear as to exactly what is happening.

One other point, I don't get on much these days as my wife is in a hospice (cancer), today was unusual, therefore you may do better starting a new thread, you can reference this one, because some posters don't respond to threads that they have previously visited and the problem is being answered, basically it is better to have one question per thread.

Brian

 

[NLillith]

Brian,

Thank you again for all your help. It was a matter of putting select then distinct after it which seemed to fix the issue.

Also, I'm sorry to hear about your wife. My prayers go out to you in this difficult time.

 

[NLillith]

So now, I need to be more specific with another query.

I have it set up where it is querying the back up date text, but I also want it to be able to pull the year too, I get the error:

"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

The SQL looks like this:
SELECT Tapes.[Backup Date Text], Tapes.[Type of Backup], Tapes.Status, Tapes.System, Tapes.[Tape #], Tapes.[Backup Date]
FROM Tapes
WHERE (((Tapes.[Backup Date Text]) Like "*" & [Enter Month] And (Tapes.[Backup Date])=[Enter Year] & "*"));

I followed this guide:

http://www.fontstuff.com/access/acctut01.htm

 

[Taruz]

Hi..

year in order to filter, use it..:

...... and ((year([Backup Date]))=[Enter Year]));

if the first field type the date, it is..:

where (((month([Backup Date Text]))=[Enter Month]) and ((year([Backup Date]))=[Enter Year]));

 

[NLillith]

Taruz,

While that would work, I am still getting the same error that I was before about the expression being too complex.

 

[Brianwarnock]

This

((month([Backup Date Text]))=[Enter Month])

looks wrong, what is [Backup Date Text] ?

Also remember that the Month function returns a number, but I guess you know that.

Brian

 

[NLillith]

The [backup date text] is the record in the table, called tapes, which was in my SQL link before.

 

[Brianwarnock]

This

WHERE (((Tapes.[Backup Date Text]) Like "*" & [Enter Month] & "*") And Year(Tapes.[Backup Date])=[Enter Year]);

would appear to make more sense


Brian

 

[NLillith]

Brian - that was exactly what I was looking to get it to do, thank you very much. Now to see if I can get the report to hold all the values.