Search

[Guest]

Hi

Im trying to make a couple of advanced search options for my database using
coding and forms,
I have two questions
1) How do I make a search that will look for a range of numbers, for
example if I want to find people who weigh 150 lbs in my database and I want
the database to show all of the forms for people who way between 145 and 155
lbs?
2) How do I make a search that will use the same criteria to search
multiple fields and bring up all matchs? For example, if I have three fields
for Pet type and I want to search each field for cat and have the database
show me every match that has cat in at least one of the fields.

I have been working on this for days and finally broke down and decided to
ask, any help would be great.

thanks,
James

 

[Guest]

How are you doing the search? If you are using a query, then to look for Cat
in three fields, you would have to have in your Where criteria:

[Field1] = "Cat" Or [Field2] = "Cat" Or [Field3] = "Cat"

To find people between 145 and 155 pounds:
[WeightField] >= 145 And [WeightField] <= 155

 

[Guest]

I was using VB coding for more simple searches, using

stLinkCriteria = "FIRCOM = '" & Me!FIRCOM.Value & "'"
DoCmd.OpenForm "tblFirings Query", , , stLinkCriteria

is there any way to do it like this, I haven't done much with access so i
dont know if this is a query or not, by the way i am trying to create a
search form so it can be used multiple times without changing the code but
still being able to change the search criteria, so puting cat or 145 lbs
won't work.

How are you doing the search? If you are using a query, then to look for Cat
in three fields, you would have to have in your Where criteria:

[Field1] = "Cat" Or [Field2] = "Cat" Or [Field3] = "Cat"

To find people between 145 and 155 pounds:
[WeightField] >= 145 And [WeightField] <= 155

Hi

Im trying to make a couple of advanced search options for my database using
coding and forms,
I have two questions
1) How do I make a search that will look for a range of numbers, for
example if I want to find people who weigh 150 lbs in my database and I want
the database to show all of the forms for people who way between 145 and 155
lbs?
2) How do I make a search that will use the same criteria to search
multiple fields and bring up all matchs? For example, if I have three fields
for Pet type and I want to search each field for cat and have the database
show me every match that has cat in at least one of the fields.

I have been working on this for days and finally broke down and decided to
ask, any help would be great.

thanks,
James

 

[Guest]

Then you will have to use Parameter Queries. Best bet is to look up
Parameters int Access Help.

I was using VB coding for more simple searches, using

stLinkCriteria = "FIRCOM = '" & Me!FIRCOM.Value & "'"
DoCmd.OpenForm "tblFirings Query", , , stLinkCriteria

is there any way to do it like this, I haven't done much with access so i
dont know if this is a query or not, by the way i am trying to create a
search form so it can be used multiple times without changing the code but
still being able to change the search criteria, so puting cat or 145 lbs
won't work.

How are you doing the search? If you are using a query, then to look for Cat
in three fields, you would have to have in your Where criteria:

[Field1] = "Cat" Or [Field2] = "Cat" Or [Field3] = "Cat"

To find people between 145 and 155 pounds:
[WeightField] >= 145 And [WeightField] <= 155

Hi

Im trying to make a couple of advanced search options for my database using
coding and forms,
I have two questions
1) How do I make a search that will look for a range of numbers, for
example if I want to find people who weigh 150 lbs in my database and I want
the database to show all of the forms for people who way between 145 and 155
lbs?
2) How do I make a search that will use the same criteria to search
multiple fields and bring up all matchs? For example, if I have three fields
for Pet type and I want to search each field for cat and have the database
show me every match that has cat in at least one of the fields.

I have been working on this for days and finally broke down and decided to
ask, any help would be great.

thanks,
James

 

[Guest]

Klatuu,
I've been all over both help files and either don't understand what they are
telling me or it doesn't do what i want it to, are there any suggestions on
how to use Parameter Queries?

-James

Then you will have to use Parameter Queries. Best bet is to look up
Parameters int Access Help.

I was using VB coding for more simple searches, using

stLinkCriteria = "FIRCOM = '" & Me!FIRCOM.Value & "'"
DoCmd.OpenForm "tblFirings Query", , , stLinkCriteria

is there any way to do it like this, I haven't done much with access so i
dont know if this is a query or not, by the way i am trying to create a
search form so it can be used multiple times without changing the code but
still being able to change the search criteria, so puting cat or 145 lbs
won't work.

How are you doing the search? If you are using a query, then to look for Cat
in three fields, you would have to have in your Where criteria:

[Field1] = "Cat" Or [Field2] = "Cat" Or [Field3] = "Cat"

To find people between 145 and 155 pounds:
[WeightField] >= 145 And [WeightField] <= 155

:

Hi

Im trying to make a couple of advanced search options for my database using
coding and forms,
I have two questions
1) How do I make a search that will look for a range of numbers, for
example if I want to find people who weigh 150 lbs in my database and I want
the database to show all of the forms for people who way between 145 and 155
lbs?
2) How do I make a search that will use the same criteria to search
multiple fields and bring up all matchs? For example, if I have three fields
for Pet type and I want to search each field for cat and have the database
show me every match that has cat in at least one of the fields.

I have been working on this for days and finally broke down and decided to
ask, any help would be great.

thanks,
James

 

[Guest]

Well, maybe a parameter query is not the way to go, a good ol' SQL statement
would be better:

Dim rst As Recordset
dim strSQL as String
dim strPet as String

strPet = "Cat"
strSQL = "SELECT * FROM mytablename " _
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '"
& strPet;"
Set Currentdb.rst = dbOpenRecordSet(srtSQL)
If rst.Recordcount = 0 Then
'No Matches
Else
rst.MoveLast
rst.MoveFirst
End If

Klatuu,
I've been all over both help files and either don't understand what they are
telling me or it doesn't do what i want it to, are there any suggestions on
how to use Parameter Queries?

-James

Then you will have to use Parameter Queries. Best bet is to look up
Parameters int Access Help.

I was using VB coding for more simple searches, using

stLinkCriteria = "FIRCOM = '" & Me!FIRCOM.Value & "'"
DoCmd.OpenForm "tblFirings Query", , , stLinkCriteria

is there any way to do it like this, I haven't done much with access so i
dont know if this is a query or not, by the way i am trying to create a
search form so it can be used multiple times without changing the code but
still being able to change the search criteria, so puting cat or 145 lbs
won't work.

:

How are you doing the search? If you are using a query, then to look for Cat
in three fields, you would have to have in your Where criteria:

[Field1] = "Cat" Or [Field2] = "Cat" Or [Field3] = "Cat"

To find people between 145 and 155 pounds:
[WeightField] >= 145 And [WeightField] <= 155

:

Hi

Im trying to make a couple of advanced search options for my database using
coding and forms,
I have two questions
1) How do I make a search that will look for a range of numbers, for
example if I want to find people who weigh 150 lbs in my database and I want
the database to show all of the forms for people who way between 145 and 155
lbs?
2) How do I make a search that will use the same criteria to search
multiple fields and bring up all matchs? For example, if I have three fields
for Pet type and I want to search each field for cat and have the database
show me every match that has cat in at least one of the fields.

I have been working on this for days and finally broke down and decided to
ask, any help would be great.

thanks,
James

 

[Guest]

Klatuu,
When I copied this code into VB it gave me an error expected end of
statement at the ; after strPet
-James

Well, maybe a parameter query is not the way to go, a good ol' SQL statement
would be better:

Dim rst As Recordset
dim strSQL as String
dim strPet as String

strPet = "Cat"
strSQL = "SELECT * FROM mytablename " _
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '"
& strPet;"
Set Currentdb.rst = dbOpenRecordSet(srtSQL)
If rst.Recordcount = 0 Then
'No Matches
Else
rst.MoveLast
rst.MoveFirst
End If

Klatuu,
I've been all over both help files and either don't understand what they are
telling me or it doesn't do what i want it to, are there any suggestions on
how to use Parameter Queries?

-James

Then you will have to use Parameter Queries. Best bet is to look up
Parameters int Access Help.

:

I was using VB coding for more simple searches, using

stLinkCriteria = "FIRCOM = '" & Me!FIRCOM.Value & "'"
DoCmd.OpenForm "tblFirings Query", , , stLinkCriteria

is there any way to do it like this, I haven't done much with access so i
dont know if this is a query or not, by the way i am trying to create a
search form so it can be used multiple times without changing the code but
still being able to change the search criteria, so puting cat or 145 lbs
won't work.

:

How are you doing the search? If you are using a query, then to look for Cat
in three fields, you would have to have in your Where criteria:

[Field1] = "Cat" Or [Field2] = "Cat" Or [Field3] = "Cat"

To find people between 145 and 155 pounds:
[WeightField] >= 145 And [WeightField] <= 155

:

Hi

Im trying to make a couple of advanced search options for my database using
coding and forms,
I have two questions
1) How do I make a search that will look for a range of numbers, for
example if I want to find people who weigh 150 lbs in my database and I want
the database to show all of the forms for people who way between 145 and 155
lbs?
2) How do I make a search that will use the same criteria to search
multiple fields and bring up all matchs? For example, if I have three fields
for Pet type and I want to search each field for cat and have the database
show me every match that has cat in at least one of the fields.

I have been working on this for days and finally broke down and decided to
ask, any help would be great.

thanks,
James

 

[Guest]

This should be all on one line, it just wraps text in this posting
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '" &
strPet;"

Klatuu,
When I copied this code into VB it gave me an error expected end of
statement at the ; after strPet
-James

Well, maybe a parameter query is not the way to go, a good ol' SQL statement
would be better:

Dim rst As Recordset
dim strSQL as String
dim strPet as String

strPet = "Cat"
strSQL = "SELECT * FROM mytablename " _
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '"
& strPet;"
Set Currentdb.rst = dbOpenRecordSet(srtSQL)
If rst.Recordcount = 0 Then
'No Matches
Else
rst.MoveLast
rst.MoveFirst
End If

Klatuu,
I've been all over both help files and either don't understand what they are
telling me or it doesn't do what i want it to, are there any suggestions on
how to use Parameter Queries?

-James

:

Then you will have to use Parameter Queries. Best bet is to look up
Parameters int Access Help.

:

I was using VB coding for more simple searches, using

stLinkCriteria = "FIRCOM = '" & Me!FIRCOM.Value & "'"
DoCmd.OpenForm "tblFirings Query", , , stLinkCriteria

is there any way to do it like this, I haven't done much with access so i
dont know if this is a query or not, by the way i am trying to create a
search form so it can be used multiple times without changing the code but
still being able to change the search criteria, so puting cat or 145 lbs
won't work.

:

How are you doing the search? If you are using a query, then to look for Cat
in three fields, you would have to have in your Where criteria:

[Field1] = "Cat" Or [Field2] = "Cat" Or [Field3] = "Cat"

To find people between 145 and 155 pounds:
[WeightField] >= 145 And [WeightField] <= 155

:

Hi

Im trying to make a couple of advanced search options for my database using
coding and forms,
I have two questions
1) How do I make a search that will look for a range of numbers, for
example if I want to find people who weigh 150 lbs in my database and I want
the database to show all of the forms for people who way between 145 and 155
lbs?
2) How do I make a search that will use the same criteria to search
multiple fields and bring up all matchs? For example, if I have three fields
for Pet type and I want to search each field for cat and have the database
show me every match that has cat in at least one of the fields.

I have been working on this for days and finally broke down and decided to
ask, any help would be great.

thanks,
James

 

[Guest]

Klatuu,
I'm still having the same problem i think it may have something to do with
the last quote i cant find an open quote for that one in the line

This should be all on one line, it just wraps text in this posting
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '" &
strPet;"

Klatuu,
When I copied this code into VB it gave me an error expected end of
statement at the ; after strPet
-James

Well, maybe a parameter query is not the way to go, a good ol' SQL statement
would be better:

Dim rst As Recordset
dim strSQL as String
dim strPet as String

strPet = "Cat"
strSQL = "SELECT * FROM mytablename " _
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '"
& strPet;"
Set Currentdb.rst = dbOpenRecordSet(srtSQL)
If rst.Recordcount = 0 Then
'No Matches
Else
rst.MoveLast
rst.MoveFirst
End If

:

Klatuu,
I've been all over both help files and either don't understand what they are
telling me or it doesn't do what i want it to, are there any suggestions on
how to use Parameter Queries?

-James

:

Then you will have to use Parameter Queries. Best bet is to look up
Parameters int Access Help.

:

I was using VB coding for more simple searches, using

stLinkCriteria = "FIRCOM = '" & Me!FIRCOM.Value & "'"
DoCmd.OpenForm "tblFirings Query", , , stLinkCriteria

is there any way to do it like this, I haven't done much with access so i
dont know if this is a query or not, by the way i am trying to create a
search form so it can be used multiple times without changing the code but
still being able to change the search criteria, so puting cat or 145 lbs
won't work.

:

How are you doing the search? If you are using a query, then to look for Cat
in three fields, you would have to have in your Where criteria:

[Field1] = "Cat" Or [Field2] = "Cat" Or [Field3] = "Cat"

To find people between 145 and 155 pounds:
[WeightField] >= 145 And [WeightField] <= 155

:

Hi

Im trying to make a couple of advanced search options for my database using
coding and forms,
I have two questions
1) How do I make a search that will look for a range of numbers, for
example if I want to find people who weigh 150 lbs in my database and I want
the database to show all of the forms for people who way between 145 and 155
lbs?
2) How do I make a search that will use the same criteria to search
multiple fields and bring up all matchs? For example, if I have three fields
for Pet type and I want to search each field for cat and have the database
show me every match that has cat in at least one of the fields.

I have been working on this for days and finally broke down and decided to
ask, any help would be great.

thanks,
James

 

[Guest]

Sorry James, you were correct
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '" &
"'strPet';"

Klatuu,
I'm still having the same problem i think it may have something to do with
the last quote i cant find an open quote for that one in the line

This should be all on one line, it just wraps text in this posting
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '" &
strPet;"

Klatuu,
When I copied this code into VB it gave me an error expected end of
statement at the ; after strPet
-James
:

Well, maybe a parameter query is not the way to go, a good ol' SQL statement
would be better:

Dim rst As Recordset
dim strSQL as String
dim strPet as String

strPet = "Cat"
strSQL = "SELECT * FROM mytablename " _
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '"
& strPet;"
Set Currentdb.rst = dbOpenRecordSet(srtSQL)
If rst.Recordcount = 0 Then
'No Matches
Else
rst.MoveLast
rst.MoveFirst
End If

:

Klatuu,
I've been all over both help files and either don't understand what they are
telling me or it doesn't do what i want it to, are there any suggestions on
how to use Parameter Queries?

-James

:

Then you will have to use Parameter Queries. Best bet is to look up
Parameters int Access Help.

:

I was using VB coding for more simple searches, using

stLinkCriteria = "FIRCOM = '" & Me!FIRCOM.Value & "'"
DoCmd.OpenForm "tblFirings Query", , , stLinkCriteria

is there any way to do it like this, I haven't done much with access so i
dont know if this is a query or not, by the way i am trying to create a
search form so it can be used multiple times without changing the code but
still being able to change the search criteria, so puting cat or 145 lbs
won't work.

:

How are you doing the search? If you are using a query, then to look for Cat
in three fields, you would have to have in your Where criteria:

[Field1] = "Cat" Or [Field2] = "Cat" Or [Field3] = "Cat"

To find people between 145 and 155 pounds:
[WeightField] >= 145 And [WeightField] <= 155

:

Hi

Im trying to make a couple of advanced search options for my database using
coding and forms,
I have two questions
1) How do I make a search that will look for a range of numbers, for
example if I want to find people who weigh 150 lbs in my database and I want
the database to show all of the forms for people who way between 145 and 155
lbs?
2) How do I make a search that will use the same criteria to search
multiple fields and bring up all matchs? For example, if I have three fields
for Pet type and I want to search each field for cat and have the database
show me every match that has cat in at least one of the fields.

I have been working on this for days and finally broke down and decided to
ask, any help would be great.

thanks,
James

 

[Guest]

Klatuu,
Now that is working, but i get a Compile error: Sub or Function not defined,
for dbOpenRecordSet(strSQL)

-James

Sorry James, you were correct
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '" &
"'strPet';"

Klatuu,
I'm still having the same problem i think it may have something to do with
the last quote i cant find an open quote for that one in the line

This should be all on one line, it just wraps text in this posting
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '" &
strPet;"

:

Klatuu,
When I copied this code into VB it gave me an error expected end of
statement at the ; after strPet
-James
:

Well, maybe a parameter query is not the way to go, a good ol' SQL statement
would be better:

Dim rst As Recordset
dim strSQL as String
dim strPet as String

strPet = "Cat"
strSQL = "SELECT * FROM mytablename " _
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '"
& strPet;"
Set Currentdb.rst = dbOpenRecordSet(srtSQL)
If rst.Recordcount = 0 Then
'No Matches
Else
rst.MoveLast
rst.MoveFirst
End If

:

Klatuu,
I've been all over both help files and either don't understand what they are
telling me or it doesn't do what i want it to, are there any suggestions on
how to use Parameter Queries?

-James

:

Then you will have to use Parameter Queries. Best bet is to look up
Parameters int Access Help.

:

I was using VB coding for more simple searches, using

stLinkCriteria = "FIRCOM = '" & Me!FIRCOM.Value & "'"
DoCmd.OpenForm "tblFirings Query", , , stLinkCriteria

is there any way to do it like this, I haven't done much with access so i
dont know if this is a query or not, by the way i am trying to create a
search form so it can be used multiple times without changing the code but
still being able to change the search criteria, so puting cat or 145 lbs
won't work.

:

How are you doing the search? If you are using a query, then to look for Cat
in three fields, you would have to have in your Where criteria:

[Field1] = "Cat" Or [Field2] = "Cat" Or [Field3] = "Cat"

To find people between 145 and 155 pounds:
[WeightField] >= 145 And [WeightField] <= 155

:

Hi

Im trying to make a couple of advanced search options for my database using
coding and forms,
I have two questions
1) How do I make a search that will look for a range of numbers, for
example if I want to find people who weigh 150 lbs in my database and I want
the database to show all of the forms for people who way between 145 and 155
lbs?
2) How do I make a search that will use the same criteria to search
multiple fields and bring up all matchs? For example, if I have three fields
for Pet type and I want to search each field for cat and have the database
show me every match that has cat in at least one of the fields.

I have been working on this for days and finally broke down and decided to
ask, any help would be great.

thanks,
James

 

[Guest]

CurrentDbOpenRecordSet(strSQL)

Klatuu,
Now that is working, but i get a Compile error: Sub or Function not defined,
for dbOpenRecordSet(strSQL)

-James

Sorry James, you were correct
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '" &
"'strPet';"

Klatuu,
I'm still having the same problem i think it may have something to do with
the last quote i cant find an open quote for that one in the line

:

This should be all on one line, it just wraps text in this posting
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '" &
strPet;"

:

Klatuu,
When I copied this code into VB it gave me an error expected end of
statement at the ; after strPet
-James
:

Well, maybe a parameter query is not the way to go, a good ol' SQL statement
would be better:

Dim rst As Recordset
dim strSQL as String
dim strPet as String

strPet = "Cat"
strSQL = "SELECT * FROM mytablename " _
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '"
& strPet;"
Set Currentdb.rst = dbOpenRecordSet(srtSQL)
If rst.Recordcount = 0 Then
'No Matches
Else
rst.MoveLast
rst.MoveFirst
End If

:

Klatuu,
I've been all over both help files and either don't understand what they are
telling me or it doesn't do what i want it to, are there any suggestions on
how to use Parameter Queries?

-James

:

Then you will have to use Parameter Queries. Best bet is to look up
Parameters int Access Help.

:

I was using VB coding for more simple searches, using

stLinkCriteria = "FIRCOM = '" & Me!FIRCOM.Value & "'"
DoCmd.OpenForm "tblFirings Query", , , stLinkCriteria

is there any way to do it like this, I haven't done much with access so i
dont know if this is a query or not, by the way i am trying to create a
search form so it can be used multiple times without changing the code but
still being able to change the search criteria, so puting cat or 145 lbs
won't work.

:

How are you doing the search? If you are using a query, then to look for Cat
in three fields, you would have to have in your Where criteria:

[Field1] = "Cat" Or [Field2] = "Cat" Or [Field3] = "Cat"

To find people between 145 and 155 pounds:
[WeightField] >= 145 And [WeightField] <= 155

:

Hi

Im trying to make a couple of advanced search options for my database using
coding and forms,
I have two questions
1) How do I make a search that will look for a range of numbers, for
example if I want to find people who weigh 150 lbs in my database and I want
the database to show all of the forms for people who way between 145 and 155
lbs?
2) How do I make a search that will use the same criteria to search
multiple fields and bring up all matchs? For example, if I have three fields
for Pet type and I want to search each field for cat and have the database
show me every match that has cat in at least one of the fields.

I have been working on this for days and finally broke down and decided to
ask, any help would be great.

thanks,
James

 

[Guest]

same error, sorry this is taking so long
James

CurrentDbOpenRecordSet(strSQL)

Klatuu,
Now that is working, but i get a Compile error: Sub or Function not defined,
for dbOpenRecordSet(strSQL)

-James

Sorry James, you were correct
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '" &
"'strPet';"

:

Klatuu,
I'm still having the same problem i think it may have something to do with
the last quote i cant find an open quote for that one in the line

:

This should be all on one line, it just wraps text in this posting
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '" &
strPet;"

:

Klatuu,
When I copied this code into VB it gave me an error expected end of
statement at the ; after strPet
-James
:

Well, maybe a parameter query is not the way to go, a good ol' SQL statement
would be better:

Dim rst As Recordset
dim strSQL as String
dim strPet as String

strPet = "Cat"
strSQL = "SELECT * FROM mytablename " _
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '"
& strPet;"
Set Currentdb.rst = dbOpenRecordSet(srtSQL)
If rst.Recordcount = 0 Then
'No Matches
Else
rst.MoveLast
rst.MoveFirst
End If

:

Klatuu,
I've been all over both help files and either don't understand what they are
telling me or it doesn't do what i want it to, are there any suggestions on
how to use Parameter Queries?

-James

:

Then you will have to use Parameter Queries. Best bet is to look up
Parameters int Access Help.

:

I was using VB coding for more simple searches, using

stLinkCriteria = "FIRCOM = '" & Me!FIRCOM.Value & "'"
DoCmd.OpenForm "tblFirings Query", , , stLinkCriteria

is there any way to do it like this, I haven't done much with access so i
dont know if this is a query or not, by the way i am trying to create a
search form so it can be used multiple times without changing the code but
still being able to change the search criteria, so puting cat or 145 lbs
won't work.

:

How are you doing the search? If you are using a query, then to look for Cat
in three fields, you would have to have in your Where criteria:

[Field1] = "Cat" Or [Field2] = "Cat" Or [Field3] = "Cat"

To find people between 145 and 155 pounds:
[WeightField] >= 145 And [WeightField] <= 155

:

Hi

Im trying to make a couple of advanced search options for my database using
coding and forms,
I have two questions
1) How do I make a search that will look for a range of numbers, for
example if I want to find people who weigh 150 lbs in my database and I want
the database to show all of the forms for people who way between 145 and 155
lbs?
2) How do I make a search that will use the same criteria to search
multiple fields and bring up all matchs? For example, if I have three fields
for Pet type and I want to search each field for cat and have the database
show me every match that has cat in at least one of the fields.

I have been working on this for days and finally broke down and decided to
ask, any help would be great.

thanks,
James

 

[Guest]

ARGGH!
Is my typing bad today or what. I think my brain has already left for the
weekend. I apologize to you for not getting it right sooner. Put a period
between CurrentDB and OpenRecordset:
CurrentDb.OpenRecordSet(strSQL)

same error, sorry this is taking so long
James

CurrentDbOpenRecordSet(strSQL)

Klatuu,
Now that is working, but i get a Compile error: Sub or Function not defined,
for dbOpenRecordSet(strSQL)

-James

:

Sorry James, you were correct
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '" &
"'strPet';"

:

Klatuu,
I'm still having the same problem i think it may have something to do with
the last quote i cant find an open quote for that one in the line

:

This should be all on one line, it just wraps text in this posting
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '" &
strPet;"

:

Klatuu,
When I copied this code into VB it gave me an error expected end of
statement at the ; after strPet
-James
:

Well, maybe a parameter query is not the way to go, a good ol' SQL statement
would be better:

Dim rst As Recordset
dim strSQL as String
dim strPet as String

strPet = "Cat"
strSQL = "SELECT * FROM mytablename " _
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '"
& strPet;"
Set Currentdb.rst = dbOpenRecordSet(srtSQL)
If rst.Recordcount = 0 Then
'No Matches
Else
rst.MoveLast
rst.MoveFirst
End If

:

Klatuu,
I've been all over both help files and either don't understand what they are
telling me or it doesn't do what i want it to, are there any suggestions on
how to use Parameter Queries?

-James

:

Then you will have to use Parameter Queries. Best bet is to look up
Parameters int Access Help.

:

I was using VB coding for more simple searches, using

stLinkCriteria = "FIRCOM = '" & Me!FIRCOM.Value & "'"
DoCmd.OpenForm "tblFirings Query", , , stLinkCriteria

is there any way to do it like this, I haven't done much with access so i
dont know if this is a query or not, by the way i am trying to create a
search form so it can be used multiple times without changing the code but
still being able to change the search criteria, so puting cat or 145 lbs
won't work.

:

How are you doing the search? If you are using a query, then to look for Cat
in three fields, you would have to have in your Where criteria:

[Field1] = "Cat" Or [Field2] = "Cat" Or [Field3] = "Cat"

To find people between 145 and 155 pounds:
[WeightField] >= 145 And [WeightField] <= 155

:

Hi

Im trying to make a couple of advanced search options for my database using
coding and forms,
I have two questions
1) How do I make a search that will look for a range of numbers, for
example if I want to find people who weigh 150 lbs in my database and I want
the database to show all of the forms for people who way between 145 and 155
lbs?
2) How do I make a search that will use the same criteria to search
multiple fields and bring up all matchs? For example, if I have three fields
for Pet type and I want to search each field for cat and have the database
show me every match that has cat in at least one of the fields.

I have been working on this for days and finally broke down and decided to
ask, any help would be great.

thanks,
James

 

[Guest]

Klatuu,
thats ok, my brain is ready for the weekend too,
that fixed this error but heres another for you,
Compile error: Method or data member not found for the .rst =
in Set CurrentDb.rst =
-James

ARGGH!
Is my typing bad today or what. I think my brain has already left for the
weekend. I apologize to you for not getting it right sooner. Put a period
between CurrentDB and OpenRecordset:
CurrentDb.OpenRecordSet(strSQL)

same error, sorry this is taking so long
James

CurrentDbOpenRecordSet(strSQL)

:

Klatuu,
Now that is working, but i get a Compile error: Sub or Function not defined,
for dbOpenRecordSet(strSQL)

-James

:

Sorry James, you were correct
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '" &
"'strPet';"

:

Klatuu,
I'm still having the same problem i think it may have something to do with
the last quote i cant find an open quote for that one in the line

:

This should be all on one line, it just wraps text in this posting
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '" &
strPet;"

:

Klatuu,
When I copied this code into VB it gave me an error expected end of
statement at the ; after strPet
-James
:

Well, maybe a parameter query is not the way to go, a good ol' SQL statement
would be better:

Dim rst As Recordset
dim strSQL as String
dim strPet as String

strPet = "Cat"
strSQL = "SELECT * FROM mytablename " _
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '"
& strPet;"
Set Currentdb.rst = dbOpenRecordSet(srtSQL)
If rst.Recordcount = 0 Then
'No Matches
Else
rst.MoveLast
rst.MoveFirst
End If

:

Klatuu,
I've been all over both help files and either don't understand what they are
telling me or it doesn't do what i want it to, are there any suggestions on
how to use Parameter Queries?

-James

:

Then you will have to use Parameter Queries. Best bet is to look up
Parameters int Access Help.

:

I was using VB coding for more simple searches, using

stLinkCriteria = "FIRCOM = '" & Me!FIRCOM.Value & "'"
DoCmd.OpenForm "tblFirings Query", , , stLinkCriteria

is there any way to do it like this, I haven't done much with access so i
dont know if this is a query or not, by the way i am trying to create a
search form so it can be used multiple times without changing the code but
still being able to change the search criteria, so puting cat or 145 lbs
won't work.

:

How are you doing the search? If you are using a query, then to look for Cat
in three fields, you would have to have in your Where criteria:

[Field1] = "Cat" Or [Field2] = "Cat" Or [Field3] = "Cat"

To find people between 145 and 155 pounds:
[WeightField] >= 145 And [WeightField] <= 155

:

Hi

Im trying to make a couple of advanced search options for my database using
coding and forms,
I have two questions
1) How do I make a search that will look for a range of numbers, for
example if I want to find people who weigh 150 lbs in my database and I want
the database to show all of the forms for people who way between 145 and 155
lbs?
2) How do I make a search that will use the same criteria to search
multiple fields and bring up all matchs? For example, if I have three fields
for Pet type and I want to search each field for cat and have the database
show me every match that has cat in at least one of the fields.

I have been working on this for days and finally broke down and decided to
ask, any help would be great.

thanks,
James

 

[Guest]

I need a nap!

Set rst = CurrentDB.OpenRecordset(strSQL)

Klatuu,
thats ok, my brain is ready for the weekend too,
that fixed this error but heres another for you,
Compile error: Method or data member not found for the .rst =
in Set CurrentDb.rst =
-James

ARGGH!
Is my typing bad today or what. I think my brain has already left for the
weekend. I apologize to you for not getting it right sooner. Put a period
between CurrentDB and OpenRecordset:
CurrentDb.OpenRecordSet(strSQL)

same error, sorry this is taking so long
James

:

CurrentDbOpenRecordSet(strSQL)

:

Klatuu,
Now that is working, but i get a Compile error: Sub or Function not defined,
for dbOpenRecordSet(strSQL)

-James

:

Sorry James, you were correct
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '" &
"'strPet';"

:

Klatuu,
I'm still having the same problem i think it may have something to do with
the last quote i cant find an open quote for that one in the line

:

This should be all on one line, it just wraps text in this posting
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '" &
strPet;"

:

Klatuu,
When I copied this code into VB it gave me an error expected end of
statement at the ; after strPet
-James
:

Well, maybe a parameter query is not the way to go, a good ol' SQL statement
would be better:

Dim rst As Recordset
dim strSQL as String
dim strPet as String

strPet = "Cat"
strSQL = "SELECT * FROM mytablename " _
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '"
& strPet;"
Set Currentdb.rst = dbOpenRecordSet(srtSQL)
If rst.Recordcount = 0 Then
'No Matches
Else
rst.MoveLast
rst.MoveFirst
End If

:

Klatuu,
I've been all over both help files and either don't understand what they are
telling me or it doesn't do what i want it to, are there any suggestions on
how to use Parameter Queries?

-James

:

Then you will have to use Parameter Queries. Best bet is to look up
Parameters int Access Help.

:

I was using VB coding for more simple searches, using

stLinkCriteria = "FIRCOM = '" & Me!FIRCOM.Value & "'"
DoCmd.OpenForm "tblFirings Query", , , stLinkCriteria

is there any way to do it like this, I haven't done much with access so i
dont know if this is a query or not, by the way i am trying to create a
search form so it can be used multiple times without changing the code but
still being able to change the search criteria, so puting cat or 145 lbs
won't work.

:

How are you doing the search? If you are using a query, then to look for Cat
in three fields, you would have to have in your Where criteria:

[Field1] = "Cat" Or [Field2] = "Cat" Or [Field3] = "Cat"

To find people between 145 and 155 pounds:
[WeightField] >= 145 And [WeightField] <= 155

:

Hi

Im trying to make a couple of advanced search options for my database using
coding and forms,
I have two questions
1) How do I make a search that will look for a range of numbers, for
example if I want to find people who weigh 150 lbs in my database and I want
the database to show all of the forms for people who way between 145 and 155
lbs?
2) How do I make a search that will use the same criteria to search
multiple fields and bring up all matchs? For example, if I have three fields
for Pet type and I want to search each field for cat and have the database
show me every match that has cat in at least one of the fields.

I have been working on this for days and finally broke down and decided to
ask, any help would be great.

thanks,
James

 

[Guest]

Klatuu

That fixed that problem, but this next one is probibaly my fault, I want the
strPet to be a user selected name, ie the user enters cat, so i put "Me![Name
of Field to enter data]" in intstead of "cat", can i do that?

the error i get is: Run-time error '3075'
Syntax Error in string query expression "WHERE fld1 = '" & strPet & "' Or
fld2 = '" & strPet & "' Or fld3 = '"
& strPet;"

-James

I need a nap!

Set rst = CurrentDB.OpenRecordset(strSQL)

Klatuu,
thats ok, my brain is ready for the weekend too,
that fixed this error but heres another for you,
Compile error: Method or data member not found for the .rst =
in Set CurrentDb.rst =
-James

ARGGH!
Is my typing bad today or what. I think my brain has already left for the
weekend. I apologize to you for not getting it right sooner. Put a period
between CurrentDB and OpenRecordset:
CurrentDb.OpenRecordSet(strSQL)

:

same error, sorry this is taking so long
James

:

CurrentDbOpenRecordSet(strSQL)

:

Klatuu,
Now that is working, but i get a Compile error: Sub or Function not defined,
for dbOpenRecordSet(strSQL)

-James

:

Sorry James, you were correct
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '" &
"'strPet';"

:

Klatuu,
I'm still having the same problem i think it may have something to do with
the last quote i cant find an open quote for that one in the line

:

This should be all on one line, it just wraps text in this posting
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '" &
strPet;"

:

Klatuu,
When I copied this code into VB it gave me an error expected end of
statement at the ; after strPet
-James
:

Well, maybe a parameter query is not the way to go, a good ol' SQL statement
would be better:

Dim rst As Recordset
dim strSQL as String
dim strPet as String

strPet = "Cat"
strSQL = "SELECT * FROM mytablename " _
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '"
& strPet;"
Set Currentdb.rst = dbOpenRecordSet(srtSQL)
If rst.Recordcount = 0 Then
'No Matches
Else
rst.MoveLast
rst.MoveFirst
End If

:

Klatuu,
I've been all over both help files and either don't understand what they are
telling me or it doesn't do what i want it to, are there any suggestions on
how to use Parameter Queries?

-James

:

Then you will have to use Parameter Queries. Best bet is to look up
Parameters int Access Help.

:

I was using VB coding for more simple searches, using

stLinkCriteria = "FIRCOM = '" & Me!FIRCOM.Value & "'"
DoCmd.OpenForm "tblFirings Query", , , stLinkCriteria

is there any way to do it like this, I haven't done much with access so i
dont know if this is a query or not, by the way i am trying to create a
search form so it can be used multiple times without changing the code but
still being able to change the search criteria, so puting cat or 145 lbs
won't work.

:

How are you doing the search? If you are using a query, then to look for Cat
in three fields, you would have to have in your Where criteria:

[Field1] = "Cat" Or [Field2] = "Cat" Or [Field3] = "Cat"

To find people between 145 and 155 pounds:
[WeightField] >= 145 And [WeightField] <= 155

:

Hi

Im trying to make a couple of advanced search options for my database using
coding and forms,
I have two questions
1) How do I make a search that will look for a range of numbers, for
example if I want to find people who weigh 150 lbs in my database and I want
the database to show all of the forms for people who way between 145 and 155
lbs?
2) How do I make a search that will use the same criteria to search
multiple fields and bring up all matchs? For example, if I have three fields
for Pet type and I want to search each field for cat and have the database
show me every match that has cat in at least one of the fields.

I have been working on this for days and finally broke down and decided to
ask, any help would be great.

thanks,
James

 

[Guest]

Yes, that is exactly how you do it.

Klatuu

That fixed that problem, but this next one is probibaly my fault, I want the
strPet to be a user selected name, ie the user enters cat, so i put "Me![Name
of Field to enter data]" in intstead of "cat", can i do that?

the error i get is: Run-time error '3075'
Syntax Error in string query expression "WHERE fld1 = '" & strPet & "' Or
fld2 = '" & strPet & "' Or fld3 = '"
& strPet;"

-James

I need a nap!

Set rst = CurrentDB.OpenRecordset(strSQL)

Klatuu,
thats ok, my brain is ready for the weekend too,
that fixed this error but heres another for you,
Compile error: Method or data member not found for the .rst =
in Set CurrentDb.rst =
-James

:

ARGGH!
Is my typing bad today or what. I think my brain has already left for the
weekend. I apologize to you for not getting it right sooner. Put a period
between CurrentDB and OpenRecordset:
CurrentDb.OpenRecordSet(strSQL)

:

same error, sorry this is taking so long
James

:

CurrentDbOpenRecordSet(strSQL)

:

Klatuu,
Now that is working, but i get a Compile error: Sub or Function not defined,
for dbOpenRecordSet(strSQL)

-James

:

Sorry James, you were correct
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '" &
"'strPet';"

:

Klatuu,
I'm still having the same problem i think it may have something to do with
the last quote i cant find an open quote for that one in the line

:

This should be all on one line, it just wraps text in this posting
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '" &
strPet;"

:

Klatuu,
When I copied this code into VB it gave me an error expected end of
statement at the ; after strPet
-James
:

Well, maybe a parameter query is not the way to go, a good ol' SQL statement
would be better:

Dim rst As Recordset
dim strSQL as String
dim strPet as String

strPet = "Cat"
strSQL = "SELECT * FROM mytablename " _
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '"
& strPet;"
Set Currentdb.rst = dbOpenRecordSet(srtSQL)
If rst.Recordcount = 0 Then
'No Matches
Else
rst.MoveLast
rst.MoveFirst
End If

:

Klatuu,
I've been all over both help files and either don't understand what they are
telling me or it doesn't do what i want it to, are there any suggestions on
how to use Parameter Queries?

-James

:

Then you will have to use Parameter Queries. Best bet is to look up
Parameters int Access Help.

:

I was using VB coding for more simple searches, using

stLinkCriteria = "FIRCOM = '" & Me!FIRCOM.Value & "'"
DoCmd.OpenForm "tblFirings Query", , , stLinkCriteria

is there any way to do it like this, I haven't done much with access so i
dont know if this is a query or not, by the way i am trying to create a
search form so it can be used multiple times without changing the code but
still being able to change the search criteria, so puting cat or 145 lbs
won't work.

:

How are you doing the search? If you are using a query, then to look for Cat
in three fields, you would have to have in your Where criteria:

[Field1] = "Cat" Or [Field2] = "Cat" Or [Field3] = "Cat"

To find people between 145 and 155 pounds:
[WeightField] >= 145 And [WeightField] <= 155

:

Hi

Im trying to make a couple of advanced search options for my database using
coding and forms,
I have two questions
1) How do I make a search that will look for a range of numbers, for
example if I want to find people who weigh 150 lbs in my database and I want
the database to show all of the forms for people who way between 145 and 155
lbs?
2) How do I make a search that will use the same criteria to search
multiple fields and bring up all matchs? For example, if I have three fields
for Pet type and I want to search each field for cat and have the database
show me every match that has cat in at least one of the fields.

I have been working on this for days and finally broke down and decided to
ask, any help would be great.

thanks,
James

 

[Guest]

I'm not sure if I am repeating this message if so ignore it, the web browser
acted funny on my last reply.

ok, so I changed the "cat" to Me!Additive, but now it gives me a new error.
My code looks like this:
Private Sub Additive_Click()
Dim rst As Recordset
Dim strSQL As String
Dim strAdditive As String

strAdditive = "Me!Additive.Value"
strSQL = "SELECT * FROM tblPropellant Query " _
& "WHERE AD1NAM = '" & strAdditive & "' Or AD2NAM = '" & strAdditive &
"' Or_ AD3NAM = '" _
& strAdditive & "' Or AD4NAM = '" & strAdditive & "' Or AD5NAM = '" _ &
"strAdditive;"

Set rst = CurrentDb.OpenRecordSet(strSQL)

If rst.RecordCount = 0 Then
'No Matches
Else
rst.MoveLast
rst.MoveFirst
End If

End Sub
and the error is this

Syntax error in string in query expression 'AD1NAM = 'Me!Additive' Or
'AD2NAM = 'Me!Additive' Or 'AD3NAM = 'Me!Additive' Or 'AD4NAM = 'Me!Additive'
Or 'AD5NAM = 'strAdditive;'

Yes, that is exactly how you do it.

Klatuu

That fixed that problem, but this next one is probibaly my fault, I want the
strPet to be a user selected name, ie the user enters cat, so i put "Me![Name
of Field to enter data]" in intstead of "cat", can i do that?

the error i get is: Run-time error '3075'
Syntax Error in string query expression "WHERE fld1 = '" & strPet & "' Or
fld2 = '" & strPet & "' Or fld3 = '"
& strPet;"

-James

I need a nap!

Set rst = CurrentDB.OpenRecordset(strSQL)

:

Klatuu,
thats ok, my brain is ready for the weekend too,
that fixed this error but heres another for you,
Compile error: Method or data member not found for the .rst =
in Set CurrentDb.rst =
-James

:

ARGGH!
Is my typing bad today or what. I think my brain has already left for the
weekend. I apologize to you for not getting it right sooner. Put a period
between CurrentDB and OpenRecordset:
CurrentDb.OpenRecordSet(strSQL)

:

same error, sorry this is taking so long
James

:

CurrentDbOpenRecordSet(strSQL)

:

Klatuu,
Now that is working, but i get a Compile error: Sub or Function not defined,
for dbOpenRecordSet(strSQL)

-James

:

Sorry James, you were correct
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '" &
"'strPet';"

:

Klatuu,
I'm still having the same problem i think it may have something to do with
the last quote i cant find an open quote for that one in the line

:

This should be all on one line, it just wraps text in this posting
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '" &
strPet;"

:

Klatuu,
When I copied this code into VB it gave me an error expected end of
statement at the ; after strPet
-James
:

Well, maybe a parameter query is not the way to go, a good ol' SQL statement
would be better:

Dim rst As Recordset
dim strSQL as String
dim strPet as String

strPet = "Cat"
strSQL = "SELECT * FROM mytablename " _
& "WHERE fld1 = '" & strPet & "' Or fld2 = '" & strPet & "' Or fld3 = '"
& strPet;"
Set Currentdb.rst = dbOpenRecordSet(srtSQL)
If rst.Recordcount = 0 Then
'No Matches
Else
rst.MoveLast
rst.MoveFirst
End If

:

Klatuu,
I've been all over both help files and either don't understand what they are
telling me or it doesn't do what i want it to, are there any suggestions on
how to use Parameter Queries?

-James

:

Then you will have to use Parameter Queries. Best bet is to look up
Parameters int Access Help.

:

I was using VB coding for more simple searches, using

stLinkCriteria = "FIRCOM = '" & Me!FIRCOM.Value & "'"
DoCmd.OpenForm "tblFirings Query", , , stLinkCriteria

is there any way to do it like this, I haven't done much with access so i
dont know if this is a query or not, by the way i am trying to create a
search form so it can be used multiple times without changing the code but
still being able to change the search criteria, so puting cat or 145 lbs
won't work.

:

How are you doing the search? If you are using a query, then to look for Cat
in three fields, you would have to have in your Where criteria:

[Field1] = "Cat" Or [Field2] = "Cat" Or [Field3] = "Cat"

To find people between 145 and 155 pounds:
[WeightField] >= 145 And [WeightField] <= 155

:

Hi

Im trying to make a couple of advanced search options for my database using
coding and forms,
I have two questions
1) How do I make a search that will look for a range of numbers, for
example if I want to find people who weigh 150 lbs in my database and I want
the database to show all of the forms for people who way between 145 and 155
lbs?
2) How do I make a search that will use the same criteria to search
multiple fields and bring up all matchs? For example, if I have three fields
for Pet type and I want to search each field for cat and have the database
show me every match that has cat in at least one of the fields.

I have been working on this for days and finally broke down and decided to
ask, any help would be great.

thanks,
James

 

[John Griffiths]

Hi

Im trying to make a couple of advanced search options for my database using
coding and forms,
I have two questions
1)

2) How do I make a search that will use the same criteria to search
multiple fields and bring up all matchs? For example, if I have three fields
for Pet type and I want to search each field for cat and have the database
show me every match that has cat in at least one of the fields.

<snip/>

This post is a little late.

You should only have 1 field for pet type, if you need to record more than
1 type of pet per main record then you need to model this
not an arbitrary number of pets.

This removal of repeating fields will put your main table into
(or further towards) meeting first normal form.

As you to not specify the information the main-table is intended to
store I shall use <main-table> to describe it,
but this is lacking as it gives no detail of what you are intending to
do.

This query will give you the information you originally wanted.

SELECT *
FROM <main-table>
WHERE <main-table>.<main-id> IN (
SELECT <pet-table>.<main-id>
FROM <pet-table>
WHERE <pet-table>.<pet-type> = "CAT"
)

Which means that now you can record information about
pets in the pet table, information about the main table in
the main table and record the fact that pets are related
to main-table by recording the id of the main table in the pet table.

I.e. if you are recording information for a vets practice then a table of
Customers and a table for Pets would seem necessary.
As a customer could have more than one pet you can add a
column (field) to the pet table to record this fact as to which customer
owns it.

PET
PetID
Name
Owner <=> put the customer id here to record who owns which
Sex
DateOfBirth

The start of something - John