UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query By Form, Access 2010    
 
   
mike60smart
post Jan 13 2017, 05:00 AM
Post#1


UtterAccess VIP
Posts: 12,176
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

I am using a Form to filter for specific values using a number of Unbound Combobox's & Textbox's

Then in the On Click Event of a Command Button I am using the following to find the specified criteria:-

If Me.txtTeam > "" Then
strCrit = strCrit & "([Team] = " & Chr(34) & Me.txtTeam & Chr(34) & ") AND "
End If

How could I change the following which would allow me to search for Numerical Values greater than a value which would be entered by the user in an Unbound Textbox on my Form:-

If Len(Me.TxtF1 & vbNullString) > 0 Then
strCrit = strCrit & "([F1] = " & Me.TxtF1 & ") AND "
End If

Any help appreciated

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Larry Larsen
post Jan 13 2017, 05:36 AM
Post#2


UA Editor + Utterly Certified
Posts: 24,097
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi Mike
What I like to do is to re-create the query using the manual method then when it's returning the results I need, I then take the SQL into forms VBA and add a little customized format..

eg:
Raw SQL
CODE
SELECT tblData.FieldThree
FROM tblData
WHERE (((tblData.FieldThree)>[input]));


Now to replicate that above in VBA:

CODE
Dim mysql As String

mysq = "SELECT tblData.FieldThree"
mysql = mysql & " FROM tblData"
mysql = mysql & " WHERE tblData.FieldThree>[input]"


The [input] is the users input value.. this could be direct or a control value from the form..

The idea here is to try and re-create the SQL using the a query first to get the results you need, then reformat in VBA in the form.

HTH's
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
mike60smart
post Jan 13 2017, 06:46 AM
Post#3


UtterAccess VIP
Posts: 12,176
Joined: 6-June 05
From: Dunbar,Scotland


Hi Larry

I tried that with 2 unbound textboxes so that the user could enter 2 values to get a Range using this:-

Dim mysql As String

mysql = "SELECT tblAcceptedQuotes.CompletedValue"
mysql = mysql & " FROM tblAcceptedQuotes"
mysql = mysql & " WHERE tblAcceptedQuotes.CompletedValue Between [txtCValue1] AND [txtCValue2]"

DoCmd.OpenReport "rptAllProjectQuotes", acPreview

Of course it does not produce the expected results.

I think the problem is that currently on the Report the value we are trying to obtain is in a SubReport based on the following SQL :-

SELECT qryQuotesforReport.tblAcceptedQuotes.AcceptedQuoteID, qryQuotesforReport.BuilderId, qryQuotesforReport.CRTNr, qryQuotesforReport.CompletedValue, qryQuotesforReport.Premium, qryQuotesforReport.tblAcceptedQuoteCharges.AcceptedQuoteID, qryQuotesforReport.ChargePeriod, qryQuotesforReport.ExtensionChanges, [Premium]+[ExtensionChanges] AS Total
FROM qryQuotesforReport;

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Larry Larsen
post Jan 15 2017, 07:03 AM
Post#4


UA Editor + Utterly Certified
Posts: 24,097
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi Mike

Your not using your VBA/SQL string in the DoCmd.OpenReport..?

eg:
CODE
DoCmd.OpenReport stDocName, acPreview, , strWhere


CODE
DoCmd.OpenReport "rptAllProjectQuotes", acPreview,,mysql


HTH's
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
mike60smart
post Jan 16 2017, 08:40 AM
Post#5


UtterAccess VIP
Posts: 12,176
Joined: 6-June 05
From: Dunbar,Scotland


Hi Larry

I tried that and now I get the following error:-

Attached File  error.JPG ( 21.67K )Number of downloads: 0


When I hit Debug it highlights this line:-

DoCmd.OpenReport "rptAllProjectQuotes", acPreview, , mysql

If I hover over the elements in the line when I hover on "acPreview" it pops up with "acPreview = 2"



--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Larry Larsen
post Jan 16 2017, 09:27 AM
Post#6


UA Editor + Utterly Certified
Posts: 24,097
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi Mike

You may need to reference those form controls..

eg:
CODE
mysql = "SELECT tblAcceptedQuotes.CompletedValue"
mysql = mysql & " FROM tblAcceptedQuotes"
mysql = mysql & " WHERE tblAcceptedQuotes.CompletedValue Between Forms!Mainform![txtCValue1] AND Forms!Mainform![txtCValue2]"

thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
mike60smart
post Jan 16 2017, 09:54 AM
Post#7


UtterAccess VIP
Posts: 12,176
Joined: 6-June 05
From: Dunbar,Scotland


Hi Larry

I have ended up modifying the query that the Report was based on.

Then by adding the following to the On Click Event it does what I need.


If Len(Me.txtCValue1 & vbNullString) > 0 Then
strCrit = strCrit & "([CompletedValue] >= " & Me.txtCValue1 & ") AND "
End If
If Len(Me.txtCValue2 & vbNullString) > 0 Then
strCrit = strCrit & "([CompletedValue] <= " & Me.txtCValue2 & ") AND "
End If


Edit Second value should be:-
If Len(Me.txtCValue2 & vbNullString) > 0 Then
strCrit = strCrit & "([CompletedValue] <= " & Me.txtCValue2 & ") AND "
End If


Many thanks for the help
cheers.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Larry Larsen
post Jan 16 2017, 10:31 AM
Post#8


UA Editor + Utterly Certified
Posts: 24,097
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


yw.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th August 2017 - 07:42 AM