Display table field through query using form

Question

Just some background information. My table, (HireHistory) has 50 columns in it (horizontal). I have a Form (HireHistoryForm) which has a 2 text boxes (HistoryMovieID and HistoryCustomerID) and a button (the button runs the query 'HireHistoryQuery')

Here's an excerpt of my data (the CustomerID's are along the top):

So what I need is so that if a number is entered into the HistoryCustomerID box, then it displays that column. e.g. if the value entered is '1', then in my query it will show all records from column 1.

If a number is entered into the HistoryMovieID box (e.g. 0001) then it displays all instances of that MovieID for the specific CustomerID's. i.e. In column 1 is the ID's, so for ID=1 it will show "0001 on 19/05/2006" then will go on to find the next instance of '0001' etc.

For the HistoryCustomerID I tried to put this into my 'Field' for the query:

=[Forms]![HireHistoryForm]![HistoryCustomerID]

But it didn't work. My query just returned a column labelled '10' and the rows were just made up of '10'.

If you could help I'd greatly appreciate it. :)

Answer 1

No offense intended (or as little as possible, anyway), but that is a horrible way to structure your data. You really need to restructure it like this:

CustomerID  MovieID HireDate
----------  ------- --------
1           0001    19/05/2006
1           0003    20/10/2003  
1           0007    13/08/2003
...     
2           0035    16/08/2012
2           0057    06/10/2012
...

If you keep your current data structure then

1. You'll go mad, and

2. It's extremely unlikely that anyone else will go anywhere near this problem.

Edit

Your revised data structure is a very slight improvement, but it still works against you. Consider that in your other question here you are essentially asking for a way to "fix" your data structure "on the fly" when you do a query.

The good news is that you can run a bit of VBA code once to convert your data structure to something workable. Start by creating your new table, which I'll call "HireHistoryV2"

ID         - AutoNumber, Primary Key
CustomerID - Number(Long Integer), Indexed (duplicates OK)
MovieID    - Text(4), Indexed (duplicates OK)
HireDate   - Date/Time, Indexed (duplicates OK)

The VBA code to copy your data to the new table would look something like this:

Function RestructureHistory()
Dim cdb As DAO.Database, rstIn As DAO.Recordset, rstOut As DAO.Recordset
Dim fld As DAO.Field, a() As String

Set cdb = CurrentDb
Set rstIn = cdb.OpenRecordset("HireHistory", dbOpenTable)
Set rstOut = cdb.OpenRecordset("HireHistoryV2", dbOpenTable)

Do While Not rstIn.EOF
    For Each fld In rstIn.Fields
        If fld.Name Like "Hire*" Then
            If Not IsNull(fld.Value) Then
                a = Split(fld.Value, " on ", -1, vbBinaryCompare)
                rstOut.AddNew
                rstOut!CustomerID = rstIn!CustomerID
                rstOut!MovieID = a(0)
                rstOut!HireDate = CDate(a(1))
                rstOut.Update
            End If
        End If
    Next
    Set fld = Nothing
    rstIn.MoveNext
Loop
rstOut.Close
Set rstOut = Nothing
rstIn.Close
Set rstIn = Nothing
Set cdb = Nothing
MsgBox "Done!"
End Function

Note: You appear to be using dd/mm/yyyy date formatting, so check the date conversions carefully to make sure that they converted properly.