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): <img alt="Data" class="b-lazy" data-src="https://i.stack.imgur.com/VZVlj.jpg" data-original="https://i.stack.imgur.com/VZVlj.jpg" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />
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:
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. :)Answer1:
No offense intended (or as little as possible, anyway), but that is a <strong><em>horrible</em></strong> way to structure your data. You <em>really</em> 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<ol><li>
You'll go mad, and</li> <li>
It's extremely unlikely that anyone else will go anywhere <em>near</em> this problem.</li> </ol><h2>Edit</h2>
Your revised data structure is a <em>very slight</em> improvement, but it still works against you. Consider that in your other question <a href="https://stackoverflow.com/questions/16073518/how-to-display-specific-row-in-access" rel="nofollow">here</a> 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 <em>once</em> 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
<strong>Note:</strong> You appear to be using dd/mm/yyyy date formatting, so <strong><em>check the date conversions carefully</em></strong> to make sure that they converted properly.