compare differnt data types

[vicgirl]

Hi,
I have trouble in comparing the two different data types.
Suppose I created a database for student essay topics.
I have one form called SearchForm to retrieve the data from a table called MainTable. This table includes Id(index), Date, Student_Name, Topic1, OtherTopic, etc. I also have another table called TopicTable only including Id and Topic field.
All data of the Topic1 field in MainTable is selected from the TopicTable (like, select one from the combobox list of topictable), while OtherTopic is input by the users, and it is not in the TopicTable.
In my searchform, I have a "Input Topic:"textbox which can search both topic fields (Topic1 and OtherTopic) from MainTable. Instead of creating one combo box and one textbox (one compare with Topic1, and the other campare with OtherTopic)
is there anyway to create just one textbox, which can compare with Topic1 and OtherTopic fields in the Maintable? The datatype in Topic1 is number (that is, Id from TopicTable). I appreciate your help.

 

[KenHigg]

Do you still need help with this?

ken

 

[vicgirl]

Yes. I didn't find a way which uses only one textbox to solve the problem yet.

Do you still need help with this?

ken

 

[KenHigg]

Just to clarify.

1. Are there any other fields in the Topic table other than the id and the topic name?

2. Do you want the user to search on the topic name in the topic table or the search on the topic id?

 

[vicgirl]

NO. There are only Id and topic name in TopicTable. The topic id is hidden to the users. In addition, the input for search criteria is a textfield. I think that the users have to search on the topic name in the topictable.
Also, Docmd.requery() is used in the VB code. All comparison are in the sql query.

Just to clarify.

1. Are there any other fields in the Topic table other than the id and the topic name?

2. Do you want the user to search on the topic name in the topic table or the search on the topic id?

 

[WayneRyan]

vicGirl,

I think what you're needing here is a UNION query.

Use the search facility here for examples.

Wayne

 

[vicgirl]

Can you give me an example? I have tried that, it didn't work out.

vicGirl,

I think what you're needing here is a UNION query.

Use the search facility here for examples.

Wayne

 

[WayneRyan]

Vic,

Here's all of your topics:

Select all Distinct topics from the main table
that aren't in the topic table. Then add to that
list all topics from the topic table that aren't
in the main table.

Select Distinct(Topic1)
From   MainTable 
Where Topic1 Not In
     (Select Topic
      From   TopicTable) UNION
Select Topic
From   TopicTable
Where  Topic Not In (
     (Select Topic1
      From   MainTable)

Wayne

 

[vicgirl]

Thanks for your help, Wayne. However, I still didn't get how this works with my search query. The search query that I have is
select Maintable.ID, Maintable.Name, Maintable.Date, Maintable.Topic1, Maintable.OtherTopic from Maintable where MainTable.OtherTopic like "'" & forms!search_form![Input Topic] & "'"or MainTable.Topic1 like "'" & forms!search_form![Input Topic] & "'"
However, MainTable.Topic1 like "'" & forms!search_form![Input Topic] & "'" doesn't work, because forms!search_form![Input Topic] is a text field, while
MainTable.Topic1 is the ID of its corresponding Topic in TopicTable.
It will explain more clearly with the sample database I posted on http://web.uvic.ca/~niedan/sample.mdb

Vic,

Here's all of your topics:

Select all Distinct topics from the main table
that aren't in the topic table. Then add to that
list all topics from the topic table that aren't
in the main table.

Select Distinct(Topic1)
From   MainTable 
Where Topic1 Not In
     (Select Topic
      From   TopicTable) UNION
Select Topic
From   TopicTable
Where  Topic Not In (
     (Select Topic1
      From   MainTable)

Wayne