70777

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): <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:

=[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. :)

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.

Recommend

  • Select EMP with max SAL from each DEPT
  • SSRS - Passing one date parameter and using it as between
  • How to export Excel file from Angular Material 5 Mat-Table (Angular Material Table)?
  • Is localization using Cookies search engine compatible?
  • How to handle error when implementing Exponential backoff function?
  • excel multiple average in single column
  • File time stamp does not change with data update
  • Is it safe to dereference end()?
  • How to enumerate word document using office interop API?
  • PHP: setting session variables through variable variables
  • Why is bash so painfully slow in boot2docker for Windows?
  • Shake iPhone to open my app. Possible?
  • HALF_PTR Windows data type
  • Is it safe to accept URL parameters for populating the `url_for` method?
  • Adding independent aspx/asmx pages into DotNetNuke
  • OpenGL - Object Transformations and VBOs
  • How can i dump blob fields from mysql tables
  • Why can't UI components be accessed from a backgroundworker?
  • xtable - background colour of added rows
  • In C what exactly happens if i use () to initialize a double dimension array instead of the {}?
  • How do I get the list of bad records that didn't load in Bigquery?
  • Hash Code in SQL Server?
  • Bootstrap (v3.3.4) glyphicons not displayed in IE when refresh page (F5)
  • Find group of records that match multiple values
  • How to autopopulate a field in SugarCRM form
  • Pycharm: Marking a folder as 'sources root' is not recursive for subfolders
  • Center align outputs in ipython notebook
  • Login not working in Firefox in Meteor
  • Inline R code in YAML for rmarkdown doesn't run
  • Thread safety of a fluent like class using clone() and non final fields
  • Retrieve list of sent friend requests from friend_request FQL table
  • MongoDb aggregation
  • Using variable in a value field in jMeter
  • Linq Objects Group By & Sum
  • Retrieving value from sql ExecuteScalar()
  • Jquery - Jquery Wysiwyg return html as a string
  • KeystoneJS: Relationships in Admin UI not updating
  • Hits per day in Google Big Query
  • Checking variable from a different class in C#
  • Unable to use reactive element in my shiny app