I have a form with a listbox on, the listbox uses a query to populate it, which returns a list of client names. The idea is, when I enter the clients name in a text box, the list is narrowed, through use of the query.
This works fine, and always has - very pleased with it, the problem comes when I decide I want to be able to search on the account number as well - which it always throws errors about.
I am using the following query (posting the sql, hope that is OK)
SELECT tblSafeKeep_Clients.SafeKeepClientID, tblSafeKeep_Clients.Title, tblSafeKeep_Clients.Initials, tblSafeKeep_Clients.Surname, Format(tblSafeKeep_Clients.DesignationNumber,"000000") AS Expr1
FROM tblSafeKeep_Clients
WHERE (((tblSafeKeep_Clients.Surname) Like "*" & [Forms]![frmSafeKeep_SelectClient]![txtSearchText] & "*")) OR (((tblSafeKeep_Clients.DesignationNumber)=[Forms]![frmSafeKeep_SelectClient]![txtSearchText]));
The problem is because I am only using ONE text box, but the surname is (obviously) text, and the account number is (obviously!) a number. SO - it throws a wobbler about it being too complex to evaluate when I try to search for a surname, trying to search for an account number works just fine.
I have a form with four combo box that will search for a record, as follow:
Field Name "Printer" , DataType "Text" Field Name Month" , DataType "Text" Field Name "Year" , DataType "Number" Field Name "Day" , DataType "Number"
code for Find Record Button:
If Not IsNull(ID) And Trim(ID) <> "" Then StrSQL = StrSQL & " And [Id] = " & [ID] End If
If Not IsNull([txtname]) And Trim([txtname]) <> "" Then If InStr([txtname], "*") = 0 Then
StrSQL = StrSQL & " And [Name] = '" & [txtname] & "'" Else StrSQL = StrSQL & " And [Name] like '" & [txtname] & "'" End If End If
'================================'
If Not IsNull([txtyear]) And Trim([txtyear]) <> "" Then
If InStr([txtyear], "*") = 0 Then
StrSQL = StrSQL & " And [Year] = '" & [txtyear] & "'" Else StrSQL = StrSQL & " And [Year] like '" & [txtyear] & "'" End If
End If
'================================'
If Not IsNull([txtmonth]) And Trim([txtmonth]) <> "" Then
If InStr([txtmonth], "*") = 0 Then
StrSQL = StrSQL & " And [Month] = '" & [txtmonth] & "'" Else StrSQL = StrSQL & " And [Month] like '" & [txtmonth] & "'" End If
End If
'================================'
If Not IsNull([txtday]) And Trim([txtday]) <> "" Then
If InStr([txtday], "*") = 0 Then StrSQL = StrSQL & " And [Day] = '" & [txtday] & "'" Else StrSQL = StrSQL & " And [Day] like '" & [txtday] & "'" End If
End If
'================================'
This is only a part of the code, I have a problem with year and day since they are numbers not text I was told that i cant use this line:
StrSQL = StrSQL & " And [Month] like '" & [txtmonth] & "'"
I was told that there is no need to use "like", am not really sure how to do it, any help will be appreciated.
Having problems getting dlookup to work in the control source field of a text box.
My form has fields : Catalog # (numeric value) and Country (drop down text selection).
I would like to query a table CatNameList for a name (text) if the catalog # and country find a match on the table. My field names on the CatNameList table are : Name, Number (to validate against the Catalog # entered on the form) and CName (to validate against the Country drop down on the form).
I am successfully able to populate the name from the CatNameList table on my form using lookup of the catalog # using this :
However, I will eventually have several catalog numbers that will be identical in the table CatNameList, thus why the country is important as the second criteria to be added into the dlookup.
I have tried for a few hours unsuccessfully to add the second portion to my dlookup.
This is what I have currently (not working) that I have been playing with, I'm sure I'm missing a quote mark, & or something simple.
I have a search form that uses a query to show results of a search, but everytime I press search everything comes up even though I have entered search parameters, even though my search requeries every time and the search used to work before I added new records today. Also when I press the query alone on the navigation pane it asks me for the parameters and then it actually works but it won't when I use my form.
I currently have a database where new records can be entered in a "New Part" Form and Records can be viewed(but not altered) in a "Part Search" Form. The forms are the same except one is allowed to edit. I have also created another form where I am trying to search the "Part Search" Form via a tracking number. I want to have a text box where users enter a tracking number then click the button to take them to the record on the "Part Search" Form.
Please bare with me as I am an Absolute Access VB code novice . I have a Form in my Access DB and I found the Following code on this site and placed it behind my search button :
Private Sub SearchButton_Click() DoCmd.OpenReport "All users", acViewPreview, , "[AUP].[First Name] = '" & Me.[Text34] & "'"
End Sub
The Search works great but only searches on the 1 Field "First Name" . How would I expand the VB code to include another 2 fields within my table . ( the other 2 fields I want to serach appear on the Form ) .
Hello, I have a text file I'm querying that stores a field as a text. ComponentID:0000000242
I'm inserting this data into a table that stores this value as a number. Thus truncating the leading zeros. ComponentID:242
I would like to create a query on the table using an InnerJoin on the text file column:ComponentID:0000000242 and the table column:ComponentID:242 as the unique identifier. Doing this join gives a case error because of the different data types.
Can format ComponentID using a query so it reads from my table as 0000000242 and do an InnerJoin to the text file. In vb String= Format(242, "0000000000") But I'm finding it difficult to get the query to format this correctly.
Unfortunately, i'm unable to change the column data type in the table structure and am looking for a work around. Is this even possible? Any ideas?
I want to have a question in my query that does not get answered by date but by a concatenated field named "MonthYearPeriod".
We always forget how many days in a month. But we remember the month and year. So for say a Sales Range of Nov 2006 to Oct 2007, we prefer to respond to the criteria question as: 11-2006 and then 10-2007.
FiscalMonth and FiscalYear are both Long Integer number fields in the table.
Query field concatenated: MonthYearPeriod: [FiscalMonth] & "-" & [FiscalYear]
criteria line: Between [Month-Year START DATE] And [Month-Year END DATE]
However, i get other crazy time periods. So this is not working. Maybe on the Query field box, I need extra coding to convert the number to text?
I assume that a Dash sign between the month and year are fine. Thanks
I have a linked table to Access 2010 which contains tracking numbers for shipments.When I link the spreadsheet to the Table, the FedEx tracking numbers are changed to for example - 9.813842152e+014 instead of 981384215196229. Is there anyway to change these back to its original format? I was thinking of creating a query where I could use Conversion Function, but it kept giving me error. Please note that both the UPS Tracking and FedEx tracking are in the same field.
I have a pair of captioned text boxes for taking the criteria for the search result. Currently the text boxes are tied with a date field. I would like to give the user's choice of a number of date fields. After some research, I believe option group control fits for this purpose. Unfortunately, I never try this function before.
i want to search a phone number in contact information table with column names - number, name, address, dob, gender, f-name, m-name etc and show them in a form with each field in separate text boxes in access 2010.
I have a table with all information on it, that is input via various forms, I then have different queries pulling information from all information to run reports off. These all work fine, my problem is my 'Search Form' - below
I have created a query that finds information from 'all information' using
Like "*" & [Forms]![SearchAll F]![txtDateRasied] & "*"
This is working on all text boxes, It only half works on the combo box's when I use
Like "*" & [Forms]![SearchAll F]![combofailureanalysis] & "*"
If a selection is made in the combo box the query brings the correct results, however, if all the fields are left blank it should bring up every record, but it doesn't do this. I am certain it is the combo box's that are causing this anomoly as when I remove the combo box criteria it works perfectly again.
I have designed a database where there are several forms that pull information from the back end table and only one used to input data. I have changed the table to allow "Year Group" to be text so that I can input "R" for our Reception. On the input form this is fine, however, on the two forms that are used to compile reports it is saying "R" isn't an allowed value.
I have attached the front end file, back end table and the two affected tables are frm_Report Builder and frm_Analysis Builder. I'm only a beginner and I'm sure this is easy but I can't seem to find where these forms say this should only be a number
I am wanting to make a text box on a form, to return a number (amount of records returned by a query)basically so if the query returns 5 records, the text box on the form will show"5",
Lets say the form is called "A", and the query is called "B" .How do I put this in the source control of the textbox ? < if this is right too ?
I've made a simple form to Login/Logout with radio buttons but the buttons only allow me to push a number as a value, in my case 1 or 2 for Login or Logout.
How would I make an update query to change those numbers to the equivalent text? Or is that not possible in the same field because that is 2 different data types?
I am creating a a text box where the user enters a text then clicks an option from the option that is used as the criteria for the search e.g. Last Name, Phone , address then a command button wil run a query.
I have a form containing 4 text Boxes txt1, txt2, txt3 and txt4
I want
txt1 show records from table empDetail having age between 18 to 25 txt2 show records from table empDetail having age between 26 to 35 txt3 show records from table empDetail having age between 36 to 45 txt4 show records from table empDetail having age between 46 to 55
Date of Birth (DOB) field etc. in one program are text - how do I make another file with the same data into number fields for Date of Birth field etc? When I copy data to file that has number fields the 09252004 is changed to 9252004. Can I get reports with the correct Date of Birth in them by moving data from text file to number file?
There is data entered monthly in file and formula has been set up for January, February etc as ---quarter: Int(([month]-1)/3)+1. I would like formula for the fiscal year for April to be counted as month 1, May - month 2, June as month 3, July as month 4, August as month 5, Sept as month 6, October as month 7, Nov as month 8, Dec as month 9, Jan as month 10, Feb as month 11 and March as month 12.
DLookup function. (this is for a stamp collection database).
On my form ("InventoryInput") I have a text box called "Catalog" for a numeric entry and a text combo box for selecting a "Country" in drop down list.
I want to query a table called "CatNameList" to get the "StampName" of the item (based on the entries of Catalog and Country) and populate that name in the text box. The fields in this table are called "StampName", "CatNumb" & "CName" respectively.
I have successfully placed the following expression in the control source of that textbox and able to populate the StampName I need based solely on the catalog number alone.
That express is : =DLookUp("StampName", "CatNameList", "CatNumb = Form![Catalog]")
So it will populate the "StampName" data to match the "Catalog" number entry just fine.
However, I need to add a second layer to incorporate the Country.
Example : There is a catalog "1" for "USA", and a catalog "1" for "Canada" but both have different "StampName".
I have been attempting to get that second piece added with no success. Here is the expression I have been trying to get to work :
Right now, the text box is just blank with the above expression. I thought it may be because there was no match found, but I have triple checked to ensure I have the spelling correct on the country name in both places.
Basically, I just need the dlookup to take the "catalog" and "country" off the form and match it to the "CatNameList" table fields of "CatNumb" and "CName" to give me "StampName" field back on the form.
I have a form with two text boxes and a button. I want to be able to type a value into the first Text box click the button and the second text box to be filled with the value which is stored in the Table.
The first Textbox is called barTxt, The second Textbox is called CustTxt The button is called SearchBtn and the Table is called BookInTable. I have been trying to use the code.
Code:
Private Sub SearchBtn_Click() DoCmd.FindRecord Me.BarTxt.Value, , True, , True CustTxt.Value = "SELECT BookInTable.Customer FROM BookInTable " & _ " WHERE Customer = """ & Nz(Me.BarTxt) & """" & _ " ORDER BY Customer" End Sub
This however instead of displaying the Value for customer which is stored in the BookInTable. Displays The code SELECT BookInTable.Customer FROMBookInTable " & _ " WHERE Customer = """ & Nz(Me.BarTxt) & """" & _ " ORDER BY Customer"
