Microsoft Access and SQL

[Cristatus]

I have hardly ever used access, and i missed the class that they taught me access, and now i need a list of reserved words in access.

Also, is it possible to do such a statement:

SELECT *
FROM Product
WHERE [Product].[Manufacturer]={some kind of variable}
{if variable is NOKIA, SONY ERICSSON, MOTOROLA, SAMSUNG, or SIEMENS,
then show only that model
else show all}

edit: here is a picture of the situation, if it makes it any easier to understand :

 

[Cristatus]

doesn't anyone know any SQL here?

 

[KLin]

What is it you're trying to do?

 

[Snapster]

If it's a variable why don't you pass * as the variable if it doesn't match the selected models. ?

 

[MrChad]

It is possible, but varchar and text variables should be enclosed in single quotes, not double quotes (e.g. 'Nokia').

 

[Snapster]

Originally posted by: MrChad
It is possible, but varchar and text variables should be enclosed in single quotes, not double quotes (e.g. 'Nokia').

If it were SQL server yeh, but this is MS Access.

 

[MrChad]

Originally posted by: Snapster

Originally posted by: MrChad
It is possible, but varchar and text variables should be enclosed in single quotes, not double quotes (e.g. 'Nokia').

If it were SQL server yeh, but this is MS Access.

It works both ways (I believe), and it's better to develop the single quote habit.

 

[Snapster]

Originally posted by: MrChad

Originally posted by: Snapster

Originally posted by: MrChad
It is possible, but varchar and text variables should be enclosed in single quotes, not double quotes (e.g. 'Nokia').

If it were SQL server yeh, but this is MS Access.

It works both ways (I believe), and it's better to develop the single quote habit.

It does, although sometimes Access is a little over happy at changing it back to double quotes.

 

[Cristatus]

hmmm....lemme try the single quotes thing today.

do you know if the "if..then..else" is possible?

 

[Snapster]

I don't know if you can do it in Access, maybe something like:

SELECT *
FROM Product
WHERE [Product].[Manufacturer] = case when @var is null then '*' else @var end

Still don't see why you need to make the db show all if not matching. If this is an application with a form front end, I'd consider just passing a * if it doesn?t match a select model.

 

[DarkManXY2G]

I'm not exactly sure what you ae trying to do but if the scenario requires you to pick a manufacturer and all corresponding data, in the query builder put this under the criteria:

Like UCase([Please Enter Manufacturer:]) & "*"

This pop open a box asking you what manufacturer you want and it will list only the corresponding data. This will also return all data associated with names regardless of spelling (ie caps or no caps)

I'm not sure if that helps you, but good luck.

 

[saeapu]

You can do this...and it will work fine...for access and sql if you are doing client side programming...

SELECT *
FROM Product
WHERE [Product].[Manufacturer='NOKIA';


For Web programming and server side you need to modify it a little:

"SELECT *
FROM Product
WHERE (([Product].[Manufacturer)='NOKIA')"