Simple Query in Access 2007

[דורון עמדי]

hello friends,

i want to know a simple question to you
i have in access 2007 table (imported from excel), in the table i have
part-numbers, and pricing and many suffixes to some of the part-numbers.
i want to have a simple query that once i enter a part number in text box in
form, that i'll get in report a table (singe table) with all variation of the
part-number.

i.e. adsp-bf531XXXXXXX (XXX=some suffixes).

p.s.
even if i enter full complete part-number to get the list of all possible
part-numbers.
thanks.

 

[Allen Browne]

Try:
Like [What part?] & "*"

I'm not clear from the last part of your message if you may have wanted:
Like Left([What part?],10) & "*"

 

[דורון עמדי]

thank you,

i did that, what i can't do is to take from the text box the string and use
it on form to display answers.
when i use query alone and use the like it works, also with text box like
you suggested in [], but i want to have a from that user enter p/n and see's
result in other form...
i dont know how to do that...

Try:
Like [What part?] & "*"

I'm not clear from the last part of your message if you may have wanted:
Like Left([What part?],10) & "*"

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

hello friends,

i want to know a simple question to you
i have in access 2007 table (imported from excel), in the table i have
part-numbers, and pricing and many suffixes to some of the part-numbers.
i want to have a simple query that once i enter a part number in text box
in
form, that i'll get in report a table (singe table) with all variation of
the
part-number.

i.e. adsp-bf531XXXXXXX (XXX=some suffixes).

p.s.
even if i enter full complete part-number to get the list of all possible
part-numbers.
thanks.

 

[Allen Browne]

This example assumes:
- a field named PartNumber
- an unbound text box named txtWotPart where you enter the criteria
- you filter the current form to matching parts.

Private Sub txtWotPart_AfterUpdate()
Dim strWhere As String
If IsNull(Me.txtWotPart) Then
MsgBox "Enter a part number, and try again."
Else
If Me.Dirty Then Me.Dirty = False
strWhere = "[PartNumber] Like """ & Me.txtWotPart & "*"""
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

thank you,

i did that, what i can't do is to take from the text box the string and
use
it on form to display answers.
when i use query alone and use the like it works, also with text box like
you suggested in [], but i want to have a from that user enter p/n and
see's
result in other form...
i dont know how to do that...

Try:
Like [What part?] & "*"

I'm not clear from the last part of your message if you may have wanted:
Like Left([What part?],10) & "*"

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

hello friends,

i want to know a simple question to you
i have in access 2007 table (imported from excel), in the table i have
part-numbers, and pricing and many suffixes to some of the
part-numbers.
i want to have a simple query that once i enter a part number in text
box
in
form, that i'll get in report a table (singe table) with all variation
of
the
part-number.

i.e. adsp-bf531XXXXXXX (XXX=some suffixes).

p.s.
even if i enter full complete part-number to get the list of all
possible
part-numbers.
thanks.

 

[דורון עמדי]

thank you allen,

since i'm very new to access, where can i write this code in?
i know in excel, but i'm very new to access 2007.
if you could please let me know how to enter text (VB) writing.
thanks

This example assumes:
- a field named PartNumber
- an unbound text box named txtWotPart where you enter the criteria
- you filter the current form to matching parts.

Private Sub txtWotPart_AfterUpdate()
Dim strWhere As String
If IsNull(Me.txtWotPart) Then
MsgBox "Enter a part number, and try again."
Else
If Me.Dirty Then Me.Dirty = False
strWhere = "[PartNumber] Like """ & Me.txtWotPart & "*"""
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

thank you,

i did that, what i can't do is to take from the text box the string and
use
it on form to display answers.
when i use query alone and use the like it works, also with text box like
you suggested in [], but i want to have a from that user enter p/n and
see's
result in other form...
i dont know how to do that...

Try:
Like [What part?] & "*"

I'm not clear from the last part of your message if you may have wanted:
Like Left([What part?],10) & "*"

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

hello friends,

i want to know a simple question to you
i have in access 2007 table (imported from excel), in the table i have
part-numbers, and pricing and many suffixes to some of the
part-numbers.
i want to have a simple query that once i enter a part number in text
box
in
form, that i'll get in report a table (singe table) with all variation
of
the
part-number.

i.e. adsp-bf531XXXXXXX (XXX=some suffixes).

p.s.
even if i enter full complete part-number to get the list of all
possible
part-numbers.
thanks.

 

[Allen Browne]

After adding the unbound text box txtWotPart to your form, set its After
Update property (on the Event tab of the properties sheet) to:
[Event Procedure]

Click the Build button (...) beside this.
Access opens the code window.

That's where the code goes.
(The first and last lines will already be there for you.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

thank you allen,

since i'm very new to access, where can i write this code in?
i know in excel, but i'm very new to access 2007.
if you could please let me know how to enter text (VB) writing.
thanks

This example assumes:
- a field named PartNumber
- an unbound text box named txtWotPart where you enter the criteria
- you filter the current form to matching parts.

Private Sub txtWotPart_AfterUpdate()
Dim strWhere As String
If IsNull(Me.txtWotPart) Then
MsgBox "Enter a part number, and try again."
Else
If Me.Dirty Then Me.Dirty = False
strWhere = "[PartNumber] Like """ & Me.txtWotPart & "*"""
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

thank you,

i did that, what i can't do is to take from the text box the string and
use
it on form to display answers.
when i use query alone and use the like it works, also with text box
like
you suggested in [], but i want to have a from that user enter p/n and
see's
result in other form...
i dont know how to do that...


:

Try:
Like [What part?] & "*"

I'm not clear from the last part of your message if you may have
wanted:
Like Left([What part?],10) & "*"

hello friends,

i want to know a simple question to you
i have in access 2007 table (imported from excel), in the table i
have
part-numbers, and pricing and many suffixes to some of the
part-numbers.
i want to have a simple query that once i enter a part number in
text
box
in
form, that i'll get in report a table (singe table) with all
variation
of
the
part-number.

i.e. adsp-bf531XXXXXXX (XXX=some suffixes).

p.s.
even if i enter full complete part-number to get the list of all
possible
part-numbers.

 

[דורון עמדי]

thank you very very much!!!!!!!
if i can just one more regarding this.
if i want to add a search button, how do do it, that once i click the button
it searches?
thanks.

After adding the unbound text box txtWotPart to your form, set its After
Update property (on the Event tab of the properties sheet) to:
[Event Procedure]

Click the Build button (...) beside this.
Access opens the code window.

That's where the code goes.
(The first and last lines will already be there for you.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

thank you allen,

since i'm very new to access, where can i write this code in?
i know in excel, but i'm very new to access 2007.
if you could please let me know how to enter text (VB) writing.
thanks

This example assumes:
- a field named PartNumber
- an unbound text box named txtWotPart where you enter the criteria
- you filter the current form to matching parts.

Private Sub txtWotPart_AfterUpdate()
Dim strWhere As String
If IsNull(Me.txtWotPart) Then
MsgBox "Enter a part number, and try again."
Else
If Me.Dirty Then Me.Dirty = False
strWhere = "[PartNumber] Like """ & Me.txtWotPart & "*"""
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

thank you,

i did that, what i can't do is to take from the text box the string and
use
it on form to display answers.
when i use query alone and use the like it works, also with text box
like
you suggested in [], but i want to have a from that user enter p/n and
see's
result in other form...
i dont know how to do that...


:

Try:
Like [What part?] & "*"

I'm not clear from the last part of your message if you may have
wanted:
Like Left([What part?],10) & "*"

hello friends,

i want to know a simple question to you
i have in access 2007 table (imported from excel), in the table i
have
part-numbers, and pricing and many suffixes to some of the
part-numbers.
i want to have a simple query that once i enter a part number in
text
box
in
form, that i'll get in report a table (singe table) with all
variation
of
the
part-number.

i.e. adsp-bf531XXXXXXX (XXX=some suffixes).

p.s.
even if i enter full complete part-number to get the list of all
possible
part-numbers.

 

[Allen Browne]

1. Add a command button to your form.

2. Set the button's On Click property to:
[Event Procedure]

3. Click the Build (...) button.

4. Move the code into here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

thank you very very much!!!!!!!
if i can just one more regarding this.
if i want to add a search button, how do do it, that once i click the
button
it searches?
thanks.

After adding the unbound text box txtWotPart to your form, set its After
Update property (on the Event tab of the properties sheet) to:
[Event Procedure]

Click the Build button (...) beside this.
Access opens the code window.

That's where the code goes.
(The first and last lines will already be there for you.)

thank you allen,

since i'm very new to access, where can i write this code in?
i know in excel, but i'm very new to access 2007.
if you could please let me know how to enter text (VB) writing.
thanks


:

This example assumes:
- a field named PartNumber
- an unbound text box named txtWotPart where you enter the criteria
- you filter the current form to matching parts.

Private Sub txtWotPart_AfterUpdate()
Dim strWhere As String
If IsNull(Me.txtWotPart) Then
MsgBox "Enter a part number, and try again."
Else
If Me.Dirty Then Me.Dirty = False
strWhere = "[PartNumber] Like """ & Me.txtWotPart & "*"""
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

thank you,

i did that, what i can't do is to take from the text box the string
and
use
it on form to display answers.
when i use query alone and use the like it works, also with text box
like
you suggested in [], but i want to have a from that user enter p/n
and
see's
result in other form...
i dont know how to do that...


:

Try:
Like [What part?] & "*"

I'm not clear from the last part of your message if you may have
wanted:
Like Left([What part?],10) & "*"

hello friends,

i want to know a simple question to you
i have in access 2007 table (imported from excel), in the table i
have
part-numbers, and pricing and many suffixes to some of the
part-numbers.
i want to have a simple query that once i enter a part number in
text
box
in
form, that i'll get in report a table (singe table) with all
variation
of
the
part-number.

i.e. adsp-bf531XXXXXXX (XXX=some suffixes).

p.s.
even if i enter full complete part-number to get the list of all
possible
part-numbers.

 

[דורון עמדי]

hello,

when i run it i see the following error:

run-time error '2455':
you entered an expression that has an invalid reference to the property dirty.
when i press debug i see:

the following line marked:
If Me.Dirty Then Me.Dirty = False
where the yellow mark is on If Me.Dirty Then
how do i solve this please?

thank you very very much!!!!!!!
if i can just one more regarding this.
if i want to add a search button, how do do it, that once i click the button
it searches?
thanks.

After adding the unbound text box txtWotPart to your form, set its After
Update property (on the Event tab of the properties sheet) to:
[Event Procedure]

Click the Build button (...) beside this.
Access opens the code window.

That's where the code goes.
(The first and last lines will already be there for you.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

thank you allen,

since i'm very new to access, where can i write this code in?
i know in excel, but i'm very new to access 2007.
if you could please let me know how to enter text (VB) writing.
thanks


:

This example assumes:
- a field named PartNumber
- an unbound text box named txtWotPart where you enter the criteria
- you filter the current form to matching parts.

Private Sub txtWotPart_AfterUpdate()
Dim strWhere As String
If IsNull(Me.txtWotPart) Then
MsgBox "Enter a part number, and try again."
Else
If Me.Dirty Then Me.Dirty = False
strWhere = "[PartNumber] Like """ & Me.txtWotPart & "*"""
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

thank you,

i did that, what i can't do is to take from the text box the string and
use
it on form to display answers.
when i use query alone and use the like it works, also with text box
like
you suggested in [], but i want to have a from that user enter p/n and
see's
result in other form...
i dont know how to do that...


:

Try:
Like [What part?] & "*"

I'm not clear from the last part of your message if you may have
wanted:
Like Left([What part?],10) & "*"

hello friends,

i want to know a simple question to you
i have in access 2007 table (imported from excel), in the table i
have
part-numbers, and pricing and many suffixes to some of the
part-numbers.
i want to have a simple query that once i enter a part number in
text
box
in
form, that i'll get in report a table (singe table) with all
variation
of
the
part-number.

i.e. adsp-bf531XXXXXXX (XXX=some suffixes).

p.s.
even if i enter full complete part-number to get the list of all
possible
part-numbers.

 

[Allen Browne]

If the form is unbound (nothing in its RecordSource property), then it will
not have a Dirty property.

But neither can you apply a filter to an unbound form.

 

[דורון עמדי]

hello Allen,

sorry i didn't follow...
i have an unbound textbox, and i put the code below in the vba, than i get
the error.
can you please suggest me what to do, i'm lost with the .dirty argument
error..

 

[Allen Browne]

Open the code window (e.g. Ctrl+G.)
Look up help for Dirty.

The Dirty property applies to a bound form.
A bound form can have an unbound text box.