Search Form Code

[f430]

hi, i have a master table with all my part information, and it has columns with part number, date, defects,...
and i am trying to write a code for my search form.
i have created a search form on Access, and it has 5 combo boxes for:
part number
defects
assembled by
and the other 2 combo boxes are for "from" and "to" date range. i have made the combo boxes for the date range to display a calendar when clicked and choose a date.

i have been trying to write a code that only requires one of these fields to be filled out and it can filter my master table.
i would appreciate any help on this
Thanks

[NeoPa]

You are talking as if you don't know the difference between VBA code and SQL code.

Earlier, we were talking about VBA code. We created a SQL filter string within this VBA to apply to the RecordSource of the form. The RecordSource would basically be a SQL string. The event procedures we've been working in though, would be VBA.

[f430]

i have been trying to manipulate the vba code into my search query but im running into a lot of problems.
i know that all i need to have are lines # 2, 6, 12, 17 from post #20
but i cant seem to have the right sql for it.
i was wondering if u had an example or anything that might help.

Thanks

[NeoPa]

I get the feeling you're not paying attention to what I post again. You keep coming back at me with stuff that makes no sense. I am trying to clarify things by asking important questions, designed to help me understand what you're talking about. If you ignore this and try to explain again in the same way as before we will get nowhere (Obviously. If I could understand you explaining it that way, there would be no need for the clever questions in the first place).

Either respond to my posts or I cannot help you. I can only help you to a certain extent to say what you need in a way that makes sense. If you ignore me then I can't help at all, and if I don't know what you're talking about I can't help technically either.

It's quite simple really.

[f430]

im not sure what questions i exactly missed, but i'll try to explain what i am having difficulty with.
for example if i take this code below:

If Not IsNull(Me.Defect) Then _
       strFilter = strFilter & " AND " & "('" & Me.Defect & "' In([Defect Code 1]," & "[Defect Code 2]," & "[Defect Code 3]))"

how can i make this code in sql language?
i have been trying to put it in the "select, where" format, but it doesn't seem to be correct

[f430]

one more question, what is the purpose of having this search query when i have the vba code for my search form. would i be able to make my search form function without the query

[NeoPa]

It seems to me you're still confused over the difference between SQL and VBA, but you ignored my post where I attempted to clarify this with you then simple carried on regardless.

Frankly, I find the first problem needs very careful handling, which is entirely impossible unless I have your full attention. Simply ignoring it and hoping it will go away is not going to make for good (any) progress. Trying to proceed from that point is unwise in the extreme. To illustrate, it's like finding someone to ask for help, and when you find they only speak Eskimo, going on to ask them to explain Einstein's Special Theory of Relativity. It matters nothing how important it is for you to understand it. You will not understand what they say anyway, and they have no idea what you're asking.

[Michael Adams]

F430, are you using the program Access by Microsoft or are you using SQL by Microsoft or perhaps you are in Visual Basic Studio or VB.net? Which one are you using, because the code is fundamentally different for each!
IE... Code for running a SQL Statement in SQL is different than running the same code in Access(VBA).

So, Which program are you using? (Is this a good question NeoPa?)

[NeoPa]

Michael Adams:
(Is this a good question NeoPa?)

It seems valid. Fundamentally, anything which can help communication is good. Communication in this thread is pretty poor overall. That is not to say anyone is being lazy or not trying, as some people just don't have good instincts or skills in that direction. Anything that can help members to understand the critical nature of clear communication, and how much depends on that, is a good thing in my book.

PS. From what's been posted already I'm fairly sure Access is the application involved. There seems to be some confusion over how SQL & VBA fit into the whole picture, but I'm confident that's where the issues lie.

[Michael Adams]

Just for a side note then, When he is coding a SQL statement does it need to start like?

ssql

[NeoPa]

No Michael (unless I misunderstand you completely, which is possible as I don't know where you're coming from with this).

SQL statements are usually resolved first as VBA strings, and are then passed to the SQL interpreter in one of a whole bundle of ways.

The most common form of SQL would be a simple SELECT statement of the form :

SELECT   [X]
       , [Y]
       , ...
FROM     [Table]
WHERE    ...
ORDER BY ...

[Michael Adams]

I ment in VBA. The code that I have used starts with ssql then the SELECT...

[NeoPa]

I can only guess you have a string variable declared as sSQL Michael. In that case yes of course, but there's no way I can know what you use as variable names without your telling me ;)