Basic Query: display on form if found question.
- Thread starter hunoob
- Start date
Here's another problem with your three cases. If you look back at the most recent version of the query (post #41), we are only checking two cases, not three - the case where the number is found in column Important_1 and the case of Important_2. I don't understand why you are now asking me to consider three cases.
Hi Jal! Thank you for your reply and help. First I would like to clear one thing. In case of post 41 we were looking for something else, namely the status of the company (L, S, C and so on). The status of the company is only mentioned in two cases..Important_0 and Important_1.
Now we are checking for the REAL name of the company. I showed you now three cases because one company can be ONLY IN ONE column (you have found 90030907 in two columns only because the real code of the company is altered, /some parts of the codenumber were deleted, others were changed for privacy issues/, and accidentaly it happend that the altered code is the same in both columns, however it is not the same company, and definetely it is not the same codenumber in the original file). So that is why I need a query which displays (in a label) the name of the company. As one company can be only in one column I showed you all the 3 cases.
I hope this way you understand, and apologies for being missleading!
Thank you in advance!
Now we are checking for the REAL name of the company. I showed you now three cases because one company can be ONLY IN ONE column (you have found 90030907 in two columns only because the real code of the company is altered, /some parts of the codenumber were deleted, others were changed for privacy issues/, and accidentaly it happend that the altered code is the same in both columns, however it is not the same company, and definetely it is not the same codenumber in the original file). So that is why I need a query which displays (in a label) the name of the company. As one company can be only in one column I showed you all the 3 cases.
I hope this way you understand, and apologies for being missleading!
Thank you in advance!
I saved the following as qryCompanyName
SELECT TOP 1 CompanyName FROM
(
SELECT [Detail0/4-Needed] as CompanyName FROM [Database]
WHERE Important_0 LIKE @NumberToFind
UNION ALL
SELECT [Detail1/5] as CompanyName FROM [Database]
WHERE Important_1 LIKE @NumberToFind
UNION ALL
SELECT [Detail2/5] as CompanyName FROM [Database]
WHERE Important_2 LIKE @NumberToFind
)
I left the original code intact - but I prepended this block of code in front of it:
'Set the company name
txtCompanyName.Value = ""
Dim cmdCompName As New ADODB.Command
cmdCompName.ActiveConnection = CurrentProject.Connection
cmdCompName.CommandText = CurrentDb.QueryDefs("qryCompanyName").SQL
cmdCompName.CommandText = fixSyntax(cmdCompName.CommandText)
cmdCompName.Parameters.Refresh
cmdCompName.Parameters("@NumberToFind").Value = txtNumber.Value
Dim rsCompName As New ADODB.Recordset
rsCompName.Open cmdCompName, , adOpenStatic, adLockReadOnly
If rsCompName.RecordCount > 0 Then Me.txtCompanyName.Value = rsCompName("CompanyName").Value
rsCompName.Close
Set rsCompName = Nothing
which depends on this block of code:
Private Function fixSyntax(SQL As String) As String
SQL = Replace(SQL, "[SELECT", "(SELECT")
SQL = Replace(SQL, "]. AS", ") As")
SQL = Replace(SQL, "; ] AS", ") As")
fixSyntax = SQL
End Function
SELECT TOP 1 CompanyName FROM
(
SELECT [Detail0/4-Needed] as CompanyName FROM [Database]
WHERE Important_0 LIKE @NumberToFind
UNION ALL
SELECT [Detail1/5] as CompanyName FROM [Database]
WHERE Important_1 LIKE @NumberToFind
UNION ALL
SELECT [Detail2/5] as CompanyName FROM [Database]
WHERE Important_2 LIKE @NumberToFind
)
I left the original code intact - but I prepended this block of code in front of it:
'Set the company name
txtCompanyName.Value = ""
Dim cmdCompName As New ADODB.Command
cmdCompName.ActiveConnection = CurrentProject.Connection
cmdCompName.CommandText = CurrentDb.QueryDefs("qryCompanyName").SQL
cmdCompName.CommandText = fixSyntax(cmdCompName.CommandText)
cmdCompName.Parameters.Refresh
cmdCompName.Parameters("@NumberToFind").Value = txtNumber.Value
Dim rsCompName As New ADODB.Recordset
rsCompName.Open cmdCompName, , adOpenStatic, adLockReadOnly
If rsCompName.RecordCount > 0 Then Me.txtCompanyName.Value = rsCompName("CompanyName").Value
rsCompName.Close
Set rsCompName = Nothing
which depends on this block of code:
Private Function fixSyntax(SQL As String) As String
SQL = Replace(SQL, "[SELECT", "(SELECT")
SQL = Replace(SQL, "]. AS", ") As")
SQL = Replace(SQL, "; ] AS", ") As")
fixSyntax = SQL
End Function
Hi Jal! The query seems to work, but there is a problem I am experiencing. I tried to change the column/field name Important_0 to Cégszám_0. I changed the name in every query and code as well, however when I run the macro I get an error (Parameter Important_0 has no default value) before displaying the LBconnections table.
The error is for this line:
rs.Open cmd, , adOpenStatic, adLockOptimistic
Could you please tell me what is the problem and how can I change the name of Important_0? (actually I will need to change the names of Important_1 and Important_2 as well) Thank you in advance!
The error is for this line:
rs.Open cmd, , adOpenStatic, adLockOptimistic
Could you please tell me what is the problem and how can I change the name of Important_0? (actually I will need to change the names of Important_1 and Important_2 as well) Thank you in advance!
Last edited:
Frankly I don't understand what's going wrong. If you removed the word "Important_0" from the query then the debugger should not be asking you questions about that word, unless you have an old version where the query was pulled from the Misc table (I hope you are no longer using that miscellaneous-table).
Hi Jal! Thank you for your continous help!
"...unless you have an old version where the query was pulled from the Misc table (I hope you are no longer using that miscellaneous-table)"
I am not really sure what do you mean? Which table?
I run '? cmd.commandText' in the immediate window and got the following error message: Run time error '424': Object required
What to do next? I am really lost..
"...unless you have an old version where the query was pulled from the Misc table (I hope you are no longer using that miscellaneous-table)"
I am not really sure what do you mean? Which table?
I run '? cmd.commandText' in the immediate window and got the following error message: Run time error '424': Object required
What to do next? I am really lost..
Set a breakpoint on a line of code by clicking in the left margin. You'll see a brown circle/dot appear on that line of code. Then run the code - it will stop on that line. Then you can go into the immediate window and poll values such as:
? cmd.CommandText
You can also hover the mouse over variables to see values.
? cmd.CommandText
You can also hover the mouse over variables to see values.
Hi Jal! Thank you so much for the advice! It is working very good. The problem was that it was left one Important_0 parameter in the query, and the method you told me showed it where it was. It seems that the application (your application) as almost fully functional. There is one more thing I would like to make, a report.
I would like that both searches to have a report form, kind of printable format. How should I do that? A button which when pressed - once the search is finished - will show the findings in a A4 printable format. The report should include the company code number, the company name and the LBconnections table as well. Please advice me how to manage that? Thank you so much for your help!
I would like that both searches to have a report form, kind of printable format. How should I do that? A button which when pressed - once the search is finished - will show the findings in a A4 printable format. The report should include the company code number, the company name and the LBconnections table as well. Please advice me how to manage that? Thank you so much for your help!
Last edited:
I'm not too good with reports, but maybe I can get you started. Let's consider the "table search" (let's call it "Multiple Number Search"). Create a report based on the results table (I forget what it was called) and name it rptMultipleNumberSearch
Then add a button to the form called "Print Report". Then run this code in the button's click event:
DoCmd.OpenReport "rptMultipleNumberSearch", acViewPreview
The other one is more complicated due to the unknown value that I called "@NumberToFind". And what do you want to report here - the contents of the listbox? Or the contents of the textbox?
Then add a button to the form called "Print Report". Then run this code in the button's click event:
DoCmd.OpenReport "rptMultipleNumberSearch", acViewPreview
The other one is more complicated due to the unknown value that I called "@NumberToFind". And what do you want to report here - the contents of the listbox? Or the contents of the textbox?
Hi Jal! Thanks to your advice I was able to make a report for the Form2. The problem is that I would desperately need one for the mainform as well.
In the mainform report I would like to display everything: the searched codenumber, the name of the company and beneath the listbox. Is that too hard? Thank you in advance!
In the mainform report I would like to display everything: the searched codenumber, the name of the company and beneath the listbox. Is that too hard? Thank you in advance!
I did some research and found out that parameters such as "@NumberToFind" apparently cannot be populated / used in Access reports. This is a very frustrating Access deficiency, in my opinion. As a result, I am now regretting that I ever introduced that parameter - and I don't have time to rewrite all that code. As a quicker solution, create a second query called qryConnections2 and, instead of using @NumberToFind, substitute:
Forms!MainForm!txtNumber
where "txtNumber" is the name of the textbox where the user types in the number. Base your report on this new qryConnections2.
Next drop a CompanyName label onto the report's header section and, in the Reports Open event, use the same type of code that I used to populate the original CompanyName label. Also drop a textbox into the header section (just like the one on the MainForm) and use code similar to mine to populate that as well.
Forms!MainForm!txtNumber
where "txtNumber" is the name of the textbox where the user types in the number. Base your report on this new qryConnections2.
Next drop a CompanyName label onto the report's header section and, in the Reports Open event, use the same type of code that I used to populate the original CompanyName label. Also drop a textbox into the header section (just like the one on the MainForm) and use code similar to mine to populate that as well.
Hi Jal! If it won't work I will post it to the reports section of this forum maybe someone there can help me! Thank you so much for your help!
Just wanted to ask you in LBconnections table how could align the amount to the middle (3rd column)? And is there a way to give a number format to the numbers in that column (instead of 35687 to be 35.687 or 35 687). Thank you for your help! Have a nice weekend!
Just wanted to ask you in LBconnections table how could align the amount to the middle (3rd column)? And is there a way to give a number format to the numbers in that column (instead of 35687 to be 35.687 or 35 687). Thank you for your help! Have a nice weekend!
The column order in a listbox follows the SELECT order. If you wanted lastName and then firstName:
SELECT lastName, firstName...
Therefore you woud have to change the SQL
The Format command works well with adding spaces and commas but I'm not sure about periods, at least in American mode this causes confusion with decimal points. (This assumes that 35687 is numeric datatype).
SELECT Format(35687, "##,###") 'returns 35,687
SELECT Format(35687, "## ###") 'returns 35 687
SELECT Format(35687, "##.###") 'returns 35687.
Thus the last item above doesn't "work" (at least not in American mode). And if it's not numeric, even the first two might not work. You might need to change it to numeric:
SELECT Format(CLng(35687), "##,###") 'returns 35,687
SELECT Format(CLng(35687), "## ###") 'returns 35 687
You could maybe do this
SELECT IIF(Len(Ctr(356887) > 2, "."Right(35687, 3)
woops, gotta go - I will fix that last line when I come back.
Assuming Num is string datatype instead of numeric (you could use Cstr(35687) if you needed to change numeric to string):
SELECT IIF(Len("35687") > 2, Left("35687", Len("35867") -3) + "." + Right(35687, 3), "35687")
The above returns 35.687 but it wouldn't be smart enough to insert a second period if needed, in other words it wouldn't change 123456789 to 123.456.789. By the way this would translate in a query like this:
SELECT IIF(Len(Amt) > 2, Left(Amt, Len(Amt) -3) + '.' + Right(Amt, 3), Amt)
Again you would have to use Cstr(Amt) if numeric. If you needed 123.456.789 you could do it using a custom function placed in a standard module (not a class module)
Public Function FormatAmt(Amt As String) As String
Amt = Strings.StrReverse(Amt)
Dim index As Long, result As String
For index = 1 To Len(Amt)
If index Mod 3 = 0 And index <> Len(Amt) Then
result = "." & Mid(Amt, index, 1) & result
Else: result = Mid(Amt, index, 1) & result
End If
Next index
FormatAmt = result
End Function
Then you could simply write:
SELECT FormatAmt(Amt)
or in our example:
SELECT FormatAmt(123456789)
which returns 123.456.789
In the code you put any char there (doesn't have to be a period), for example if I wanted to insert a percent sign I would change the key line to this:
result = "%" & Mid(Amt, index, 1) & result
SELECT IIF(Len("35687") > 2, Left("35687", Len("35867") -3) + "." + Right(35687, 3), "35687")
The above returns 35.687 but it wouldn't be smart enough to insert a second period if needed, in other words it wouldn't change 123456789 to 123.456.789. By the way this would translate in a query like this:
SELECT IIF(Len(Amt) > 2, Left(Amt, Len(Amt) -3) + '.' + Right(Amt, 3), Amt)
Again you would have to use Cstr(Amt) if numeric. If you needed 123.456.789 you could do it using a custom function placed in a standard module (not a class module)
Public Function FormatAmt(Amt As String) As String
Amt = Strings.StrReverse(Amt)
Dim index As Long, result As String
For index = 1 To Len(Amt)
If index Mod 3 = 0 And index <> Len(Amt) Then
result = "." & Mid(Amt, index, 1) & result
Else: result = Mid(Amt, index, 1) & result
End If
Next index
FormatAmt = result
End Function
Then you could simply write:
SELECT FormatAmt(Amt)
or in our example:
SELECT FormatAmt(123456789)
which returns 123.456.789
In the code you put any char there (doesn't have to be a period), for example if I wanted to insert a percent sign I would change the key line to this:
result = "%" & Mid(Amt, index, 1) & result
