Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 12th, 2007, 03:49 PM
Authorized User
Points: 336, Level: 6
Points: 336, Level: 6 Points: 336, Level: 6 Points: 336, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2006
Location: , , .
Posts: 73
Thanks: 6
Thanked 0 Times in 0 Posts
Default Help Getting Search to Work

Hello All:

I am trying to use parameters in combo boxes to get search criteria for a form. I got all of the text fields working fine but I can't get a number and date field to work. Here are my variables statements:

where = where & " AND [RecordNumber] = " + Me![scboRecordNumber]
where = where & " AND [EnteredDate] = #" + Me![scboEnteredDate] + " #"

I am just selecting the RecordNumber (1) and/or EnteredDate (07/12/2007) in a drop down from a combo box but I get the data mismatch error. The fields in the table are Autonumber and DateTime respectively.

Any ideas??

Thanks,
Tony

Reply With Quote
  #2 (permalink)  
Old July 12th, 2007, 06:02 PM
Registered User
 
Join Date: Jul 2007
Location: old bridge, nj, USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to tekgod
Default

Try this replacement code...


where = where & " AND [RecordNumber] = " & Me![scboRecordNumber]
where = where & " AND [EnteredDate] = #" & Me![scboEnteredDate] & "#"


The + symbol is used for math. The & is for concatenation. Since both fields are of different types you will get an error when you + them together.

Hope this helps.



Reply With Quote
  #3 (permalink)  
Old July 12th, 2007, 10:32 PM
Authorized User
Points: 336, Level: 6
Points: 336, Level: 6 Points: 336, Level: 6 Points: 336, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2006
Location: , , .
Posts: 73
Thanks: 6
Thanked 0 Times in 0 Posts
Default

This isn't working. I tried adding the record number by itself and rem'd out the [EnteredDate] and the record doesn't pull up anything when I select a record number in the dropdown. If I use both [RecordNumber] and use the [EnteredDate] it doesn't pull up either. I am getting runtime 3075.

The query printout is:

Syntax error in Date in query expression [RecordNumber] = 3 AND [EnteredDate] = ##

Reply With Quote
  #4 (permalink)  
Old July 13th, 2007, 07:38 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

This looks like it isn't picking up a value:

Me![scboEnteredDate]

Use Me. and see is the combo box auto completes. Then use that name. For example, if you type in Me.scbo it should auto complete and give you the combo box names on your form.

Then instead of passing the value directly to the string, you should be doing some value checking. For example:

Dim dtDate As Date

If IsNull(Me.scboEnteredDate) Or Me.scboEnteredDate = "" Then
   MsgBox "Please select a date.", vbCritical
   Exit Sub
Else
   dtDate = Me.scboEnteredDate
End If

where = where & " AND [EnteredDate] = #" & dtDate & "#"

Did that help any?



mmcdonal
Reply With Quote
  #5 (permalink)  
Old July 13th, 2007, 08:29 AM
Authorized User
Points: 336, Level: 6
Points: 336, Level: 6 Points: 336, Level: 6 Points: 336, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2006
Location: , , .
Posts: 73
Thanks: 6
Thanked 0 Times in 0 Posts
Default

I have never had a problem with the values for the RecordNumber and EnteredDate displaying in the dropdown of the combo box The problem has been data mismatch errors.

When i tried it your way previously I still got the values in the dropdown but didn't return anything using the [RecordNumber] and using the [EnteredDate] gave the syntax error.

With your new suggestion, nothing is displayed in using either/or both parameters but no errors.

Thanks for all of your help.
Tony

Reply With Quote
  #6 (permalink)  
Old July 13th, 2007, 08:42 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Now I am not sure what you are doing.

Are you trying to pass parameters to a form?

If so, pass the parameters to a query, and then open the form on the query instead. Take the values in the query criteria from the first form.

Did this help? I am not sure why you are talking about combo boxes not displaying data. Also, it seems to me that if you are using a PK as a parameter, then no other parameters are needed.

mmcdonal
Reply With Quote
  #7 (permalink)  
Old July 13th, 2007, 09:04 AM
Authorized User
Points: 336, Level: 6
Points: 336, Level: 6 Points: 336, Level: 6 Points: 336, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2006
Location: , , .
Posts: 73
Thanks: 6
Thanked 0 Times in 0 Posts
Default

I was talking about the data displayed in the combo boxes because I misread your message..Sorry.

Here is the code snippet from the subroutine. Maybe it will show you something. You have to remember that it works great for the text strings just not the numbers or date.

Tony
================================================== =====================

Private Sub scmdSearch_Click()
    Dim db As Database
    Dim QD As QueryDef
    Dim where As Variant

    Set db = CurrentDb()

    On Error Resume Next
    db.querydefs.Delete ("Dynamic_Query")
    On Error GoTo 0

    where = Null
    where = where & " AND [RecordNumber] = " & Me![scboRecordNumber]
    where = where & " AND [EnteredBy] = '" + Me![scboEnteredBy] + "'"
    where = where & " AND [EnteredDate] = #" & Me.scboEnteredDate & "#"
    where = where & " AND [RequestingFacility] = '" + Me![scboRequestingFacility] + "'"
    where = where & " AND [RequestedBy] = '" + Me![scboRequestedBy] + "'"
    where = where & " AND [CurrentContact] = '" + Me![scboCurrentContact] + "'"
    where = where & " AND [OrdersetName] = '" + Me![scboOrdersetName] + "'"
    where = where & " AND [OrderItemName] = '" + Me![scboOrderItemName] + "'"
    where = where & " AND [OrderSection] = '" + Me![scboOrderSection] + "'"
    where = where & " AND [OrdersetFormat] = '" + Me![scboOrdersetFormat] + "'"
    where = where & " AND [OrdersetType] = '" + Me![scboOrdersetType] + "'"
    'where = where & " AND [RequestedDate] = + Me![scboRequestedDate] + "
    where = where & " AND [ChangeApproved] = '" + Me![scboChangeApproved] + "'"
    'where = where & " AND [ChangeApprovalDate] = + Me![scboChangeApprovalDate] + "
    where = where & " AND [AssignedAnalyst] = '" + Me![scboAssignedAnalyst] + "'"
    'where = where & " AND [CompletedDate] = + Me![scboCompletedDate] "

    MsgBox "Select * from BHCS_Change_Request " & (" where " + Mid(where, 6) & ";")
    Set QD = db.CreateQueryDef("Dynamic_Query", _
    "Select * from BHCS_Change_Request " & (" where " + Mid(where, 6) & ";"))
    DoCmd.OpenQuery "Dynamic_Query", , acReadOnly

End Sub

Reply With Quote
  #8 (permalink)  
Old July 13th, 2007, 09:09 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Again, if you are taking the recordnumber field, which I assume is a PK as a parameter, then why take any other parameters since this one parameter alone will only return one record anyway?

That would be

Dim iRN As Integer

iRN = Me.RecordNumber

"SELECT * FROM BHCS_Change_Request WHERE [RecordNumber] = " & iRN

Note no single quotes with a number.

mmcdonal
Reply With Quote
  #9 (permalink)  
Old July 13th, 2007, 09:28 AM
Authorized User
Points: 336, Level: 6
Points: 336, Level: 6 Points: 336, Level: 6 Points: 336, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2006
Location: , , .
Posts: 73
Thanks: 6
Thanked 0 Times in 0 Posts
Default

Because there are going to be numerous records in the database and they might not want to look up via a record number but that option is there. They might want to look up all the records that were entered on a specified date or all the records from a requesting facility.

As I mentioned it works great if I comment out the [scboRecordNumber] line and all of the scbo[*Date] lines.

Yes the record number is the PK and it is an 'autonumber'


Thanks,
Tony

Reply With Quote
  #10 (permalink)  
Old July 14th, 2007, 06:08 AM
Authorized User
 
Join Date: Mar 2007
Location: , , United Kingdom.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Okay, so you want to do a "query by example" screen - all of the combo box entries are possible search criteria, but the user is not expected to choose values for all of them, just the ones he wants - is that right?

If so, then I can understand why your current code is failing to return any records. You have specified each and every combo box in the query - so the ones that the user does not populate will evaluate to an empty string. There will of course be no records on your database where most of the field values are empty!

What you need is to check for an entry from the user before adding each clause to the WHERE predicate. In simpler terms, something like this:

    where = where & " AND [RecordNumber] = " & Me![scboRecordNumber]
    If Me![scboEnteredBy].ListIndex <> -1 Then where = where & " AND [EnteredBy] = '" + Me![scboEnteredBy] + "'"
    If Me.scboEnteredDate.ListIndex <> -1 Then where = where & " AND [EnteredDate] = #" & Me.scboEnteredDate & "#"

and so on.

Hope this helps!

Richard


Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Search button doesn't search Access DB cbones Visual Studio 2008 1 October 27th, 2008 07:36 PM
New text search doesn't preselect search string planoie Visual Studio 2005 0 July 23rd, 2007 06:47 AM
Search Engine for Full-text Search Kala ASP.NET 1.0 and 1.1 Professional 2 August 29th, 2004 02:16 AM
Getting a search txt box to work JJ Access VBA 4 October 23rd, 2003 10:19 AM
Search engine that search through local drive! wenzation Classic ASP Basics 0 August 26th, 2003 09:15 PM



All times are GMT -4. The time now is 11:01 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.