SQL statement

[Guest]

Hello:
I am using the following SQL statement to combine the data from 2 fields

Select [company id], [company name] from [company table]
UNION
Select [company id], [old name] from [history table]
ORDER BY [company name]

but if my tables look like this:

Company table History table
company id company name company id old name
123 A 123 ZZ
345 B
Then my output is
123
123
345
123 A
345 B
123 ZZ
Why do I get the all the numbers with no name and then all the numbers with
name
Thanks
J

 

[TC]

The bank names come first because you have explicitly said to sort the
output by [company name], and blank names come before non-blank ones!

If you wanted to put the blank names at the end, change the ORDER BY
part to this:

...... ORDER BY IIF (ISNULL([company name]), "ZZZ", [company name])

If you wanted to *exclude* the records with blank names:

.... WHERE [company name] IS NOT NULL
ORDER BY ...

HTH,
TC (MVP Access)
htp://tc2.atspace.com

 

[Guest]

Perfect! Thanks TC.

 

[Guest]

If I amend the SQL statement to include Corporation ID and I create a combo
box with three fields - Company name, Company ID and Corporation ID how can I
use it to open a record based on the name the user types or selects. For
example, I use a combo box with Company ID and Corporation ID to open the
record using the following code.

If Not IsNull(Me.Combo7) Then
strWhere = "[Corporation ID] = """ & Me.Combo7.Column(1) & """"
DoCmd.OpenForm "Main form", WhereCondition:=strWhere
strWhere = "[Company ID] = """ & Me.Combo7 & """"
With Forms("Main form")![Company].Form
Set rs = .RecordsetClone
rs.FindFirst strWhere
Me.Combo7 = Null
.Bookmark = rs.Bookmark
How can I do this with the name?
Thanks
J.

 

[TC]

If I amend the SQL statement to include Corporation ID and I create a combo
box with three fields - Company name, Company ID and Corporation ID how can I
use it to open a record based on the name the user types or selects. For
example, I use a combo box with Company ID and Corporation ID to open the
record using the following code.

If Not IsNull(Me.Combo7) Then

That's fine, you only want to proceed if they have selected an entry in
Combo7. A comment in passing: it's best to give each control a
meaningful name; eg. cboCorporations, or somesuch. Meaningfull names
are much easier to understand, in the code, compared to Combo7 etc.

strWhere = "[Corporation ID] = """ & Me.Combo7.Column(1) & """"

Ok, you've built a where condition like: [CorporationID]="abc"

DoCmd.OpenForm "Main form", WhereCondition:=strWhere

You've opened the form, using that where condition. Does that work?
Does it position form "Main form" to the record with the selected
corporation ID? Is that what you mean that statement to do?

strWhere = "[Company ID] = """ & Me.Combo7 & """"

Ok, you've set up another condition. But I'm curious why the same value
(the selected value in Combo7) could be a Corporation ID in one table,
and a Company ID in another. Is that correct? Any reason why you did
not use the same field name in both tables?

With Forms("Main form")![Company].Form

That would get a reference to the form within the subform *control*
named "Company", in form "Main form". Yes? Again, a better naming
convention would help you out here. use an "sf" prefix for subforms.
Eg. sfCompany is a subform control related to companies.

Set rs = .RecordsetClone
rs.FindFirst strWhere
Me.Combo7 = Null
.Bookmark = rs.Bookmark

That should posiion that subform, to the first record where [Company
ID] = the value that you selected in Combo7. Is that what you meant? If
so, does it work?

So I'm not quite sure which parts are working, & which are not. Hlp me
out there.

I'll have to go for the rest of the day. So I won't see your answers
until late tonight, or tomorrow.

Cheers,
TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

Yes, the code works. There is a 1-to-many relationship between Corporation
and Company so Yes, Company is a subform. I already have a search by Company
ID which works using the code. So, if the Corporation is Corp1 and the
Company ID is 123, when the user enters 123 in the combo box which has a list
like
111 Corp4
114 Corp4
123 Corp1
122 Corp3
and then the user clicks the find button, the Main form opens(displays Corp1
as the corporation and 123 as the Company) so yes Main form is the
Corparation or parent form and Company is the subform. I see what you mean
about making the names clearer.
So what I want to do is allow the user to search by Company name(not a
primary key) Combo box list looks something like this
Cname1 111 Corp4
Cname2 114 Corp4
Cname3 123 Corp1 etc. How can I do that using similar code.
Hope that helps.
J

If I amend the SQL statement to include Corporation ID and I create a combo
box with three fields - Company name, Company ID and Corporation ID how can I
use it to open a record based on the name the user types or selects. For
example, I use a combo box with Company ID and Corporation ID to open the
record using the following code.

If Not IsNull(Me.Combo7) Then

That's fine, you only want to proceed if they have selected an entry in
Combo7. A comment in passing: it's best to give each control a
meaningful name; eg. cboCorporations, or somesuch. Meaningfull names
are much easier to understand, in the code, compared to Combo7 etc.

strWhere = "[Corporation ID] = """ & Me.Combo7.Column(1) & """"

Ok, you've built a where condition like: [CorporationID]="abc"

DoCmd.OpenForm "Main form", WhereCondition:=strWhere

You've opened the form, using that where condition. Does that work?
Does it position form "Main form" to the record with the selected
corporation ID? Is that what you mean that statement to do?

strWhere = "[Company ID] = """ & Me.Combo7 & """"

Ok, you've set up another condition. But I'm curious why the same value
(the selected value in Combo7) could be a Corporation ID in one table,
and a Company ID in another. Is that correct? Any reason why you did
not use the same field name in both tables?

With Forms("Main form")![Company].Form

That would get a reference to the form within the subform *control*
named "Company", in form "Main form". Yes? Again, a better naming
convention would help you out here. use an "sf" prefix for subforms.
Eg. sfCompany is a subform control related to companies.

Set rs = .RecordsetClone
rs.FindFirst strWhere
Me.Combo7 = Null
.Bookmark = rs.Bookmark

That should posiion that subform, to the first record where [Company
ID] = the value that you selected in Combo7. Is that what you meant? If
so, does it work?

So I'm not quite sure which parts are working, & which are not. Hlp me
out there.

I'll have to go for the rest of the day. So I won't see your answers
until late tonight, or tomorrow.

Cheers,
TC (MVP Access)
http://tc2.atspace.com