How to include "/"

[Guest]

Hi all,

I have the following query:

SELECT Orders.OrderID, Orders.InvVWD
FROM Orders
WHERE (((Orders.InvVWD) Like "*" & [Enter Invoice Number] & "*"));

and I need the query NOT to work when the number is part of a number:

For example:

09123456
or
123456789

I hope is clear.

Thanks for any help,
Emilio

 

[Guest]

Oops!

Sorry I have the wrong title.

 

[Guest]

If you want the query to return the exact match of the parameter entered by
the user, replace the Like with =, and remove the *

SELECT Orders.OrderID, Orders.InvVWD
FROM Orders
WHERE Orders.InvVWD = [Enter Invoice Number]

--
\\// Live Long and Prosper \\//
BS"D

Oops!

Sorry I have the wrong title.

Hi all,

I have the following query:

SELECT Orders.OrderID, Orders.InvVWD
FROM Orders
WHERE (((Orders.InvVWD) Like "*" & [Enter Invoice Number] & "*"));

and I need the query NOT to work when the number is part of a number:

For example:

09123456
or
123456789

I hope is clear.

Thanks for any help,
Emilio

 

[Van T. Dinh]

Please explain what you mean by " ...when the number is part of a number"
and give a clearer example with respect to your criteria.

My guess is that if the user enters, says, "123" as the value for the
paramter, you don't want to pick the invoices whose invoice numbers include
the String "123"??? If this is the case, you can use:

....
WHERE (((Orders.InvVWD) Not Like "*" & [Enter Invoice Number] & "*"))

 

[Guest]

Thanks for your help,

I tried not to complicate the question and I screwed it up.

Say the invoice I am looking for is: 123

Then only bring me these 3 options:

/123
123
123/

This is because when is more than 1 invoice for the same supplier we
separate them with"/" and it could be before or after the invoice i am
looking for, or it could be the only invoice.

I hope this clarifies it.

Thanks again,
Emilio

Please explain what you mean by " ...when the number is part of a number"
and give a clearer example with respect to your criteria.

My guess is that if the user enters, says, "123" as the value for the
paramter, you don't want to pick the invoices whose invoice numbers include
the String "123"??? If this is the case, you can use:

....
WHERE (((Orders.InvVWD) Not Like "*" & [Enter Invoice Number] & "*"))

--
HTH
Van T. Dinh
MVP (Access)

Hi all,

I have the following query:

SELECT Orders.OrderID, Orders.InvVWD
FROM Orders
WHERE (((Orders.InvVWD) Like "*" & [Enter Invoice Number] & "*"));

and I need the query NOT to work when the number is part of a number:

For example:

09123456
or
123456789

I hope is clear.

Thanks for any help,
Emilio

 

[John Spencer]

SELECT Orders.OrderID, Orders.InvVWD
FROM Orders
WHERE "/" & Orders.InvVWD & "/" Like "*/" & [Enter Invoice Number] & "*/"))

This will match records for 123 where the InvVWD field contains
123
456/123/459
123/458
458/123

If your field contains
458/ 123/ 999
Then it will fail.

Your data design should be changed to have one record for each invVWD in a
separate table.
Table VWD
OrderID - reference to the order id number in the Orders table
InvVWD - one of the values you are currently storing in the InvVWD field in
the Orders table.

Thanks for your help,

I tried not to complicate the question and I screwed it up.

Say the invoice I am looking for is: 123

Then only bring me these 3 options:

/123
123
123/

This is because when is more than 1 invoice for the same supplier we
separate them with"/" and it could be before or after the invoice i am
looking for, or it could be the only invoice.

I hope this clarifies it.

Thanks again,
Emilio

Please explain what you mean by " ...when the number is part of a number"
and give a clearer example with respect to your criteria.

My guess is that if the user enters, says, "123" as the value for the
paramter, you don't want to pick the invoices whose invoice numbers
include
the String "123"??? If this is the case, you can use:

....
WHERE (((Orders.InvVWD) Not Like "*" & [Enter Invoice Number] & "*"))

--
HTH
Van T. Dinh
MVP (Access)

Hi all,

I have the following query:

SELECT Orders.OrderID, Orders.InvVWD
FROM Orders
WHERE (((Orders.InvVWD) Like "*" & [Enter Invoice Number] & "*"));

and I need the query NOT to work when the number is part of a number:

For example:

09123456
or
123456789

I hope is clear.

Thanks for any help,
Emilio

 

[Guest]

Thanks a lot John!

And yes, I need a major overhaul in the Orders table.

Emilio

SELECT Orders.OrderID, Orders.InvVWD
FROM Orders
WHERE "/" & Orders.InvVWD & "/" Like "*/" & [Enter Invoice Number] & "*/"))

This will match records for 123 where the InvVWD field contains
123
456/123/459
123/458
458/123

If your field contains
458/ 123/ 999
Then it will fail.

Your data design should be changed to have one record for each invVWD in a
separate table.
Table VWD
OrderID - reference to the order id number in the Orders table
InvVWD - one of the values you are currently storing in the InvVWD field in
the Orders table.

Thanks for your help,

I tried not to complicate the question and I screwed it up.

Say the invoice I am looking for is: 123

Then only bring me these 3 options:

/123
123
123/

This is because when is more than 1 invoice for the same supplier we
separate them with"/" and it could be before or after the invoice i am
looking for, or it could be the only invoice.

I hope this clarifies it.

Thanks again,
Emilio

Please explain what you mean by " ...when the number is part of a number"
and give a clearer example with respect to your criteria.

My guess is that if the user enters, says, "123" as the value for the
paramter, you don't want to pick the invoices whose invoice numbers
include
the String "123"??? If this is the case, you can use:

....
WHERE (((Orders.InvVWD) Not Like "*" & [Enter Invoice Number] & "*"))

--
HTH
Van T. Dinh
MVP (Access)



Hi all,

I have the following query:

SELECT Orders.OrderID, Orders.InvVWD
FROM Orders
WHERE (((Orders.InvVWD) Like "*" & [Enter Invoice Number] & "*"));

and I need the query NOT to work when the number is part of a number:

For example:

09123456
or
123456789

I hope is clear.

Thanks for any help,
Emilio