make a query some particular fields (1 Viewer)

[Dhanu]

hi all,
I created a table and made a form according to the table.now I want to run a query (or don't know if there is another easy way ) to search some details in the table and i want to print them too. Eg: by customer name, by dates, by customer sure name, by package etc...

i already did this parameter to search date , Between [Forms]![CustomerSearchF]![txtDatadiinizio] And [Forms]![CustomerSearchF]![txtDatadifine]

so now I want to do the same thing with other fields also. I'm a new user to MS Access. if someone helps me to get this problem sorted out i will be a big help for me.
pls refer attached file also.you can get an idea what i want.

thanks.

 

[plog]

The best way to do this is not with a query, but with a report. You build a report based on your table, then you open the report via DoCmd.OpenReport (https://msdn.microsoft.com/en-us/vba/access-vba/articles/docmd-openreport-method-access). That allows you to pass a filter to the report. You build that filter based on your input form. the user clicks the button and gets the report with just the data they want.

 

[Pat Hartman]

Your form can create a custom criteria string and pass that to the report using the method suggested by plog.

Dim strWhere as String
Dim QUOTE as String
QUOTE = """"
strWhere = "YourDateFieldName Between #" & Me.txtDatadiinizio & "# And #" & Me.txtDatadifine & "#"
If Me.txtNome & "" = "" Then
Else
    strWhere = strWhere & " AND Nome = " & QUOTE & Me.txtNome & QUOTE
End If
If Me.txtcogNome & "" = "" Then
Else
    strWhere = strWhere & " AND cogNome = " & QUOTE & Me.txtcogNome & QUOTE
End If
....

 

[arnelgp]

it depends on what you want to show in your query:
for somewhat exact match:

([Date] Between Forms![CustomerSearchF]!txtDatadiinizio And Forms![CustomerSearchF]!txtDatadifine) And
([name] Like '*" & [Forms]![CustomerSearchF]!txtNome & "*'") And
([Cognome] Like '*" & [Forms]![CustomerSearchF]!txtCognome & "*'") And
([Paese] Like '*" & [Forms]![CustomerSearchF]!txtPaese & "*'") And
([Tour e Escursioni] Like '*" & [Forms]![CustomerSearchF]!txtTour_e_Escursioni & "*'") And
([Tour Operator] Like '*" & [Forms]![CustomerSearchF]!txtTour_Operator & "*'") And
([Cadice Operatore] Like '*" & [Forms]![CustomerSearchF]!txtCadice_Operatore & "*'")


for partial match:

([Date] Between Forms![CustomerSearchF]!txtDatadiinizio And Forms![CustomerSearchF]!txtDatadifine) And
(([name] Like '*" & [Forms]![CustomerSearchF]!txtNome & "*'") Or
([Cognome] Like '*" & [Forms]![CustomerSearchF]!txtCognome & "*'") Or
([Paese] Like '*" & [Forms]![CustomerSearchF]!txtPaese & "*'") Or
([Tour e Escursioni] Like '*" & [Forms]![CustomerSearchF]!txtTour_e_Escursioni & "*'") Or
([Tour Operator] Like '*" & [Forms]![CustomerSearchF]!txtTour_Operator & "*'") Or
([Cadice Operatore] Like '*" & [Forms]![CustomerSearchF]!txtCadice_Operatore & "*'"))

 

[Pat Hartman]

LIKE should not be your "go to" relational operator. LIKE is a STRING operation and should only be used on text data types and only when you are searching for a partial match. It is most often used for searching names and addresses or other free form text blobs. Keep in mind that in most RDBMS', LIKE will force a full table scan and that means that the larger your table is, the slower the query will be. NEVER use LIKE if you have a full value for example, if you picked something from a combo or list box, those controls always return the full value.

 

[Dhanu]

it depends on what you want to show in your query:
for somewhat exact match:

([Date] Between Forms![CustomerSearchF]!txtDatadiinizio And Forms![CustomerSearchF]!txtDatadifine) And
([name] Like '*" & [Forms]![CustomerSearchF]!txtNome & "*'") And
([Cognome] Like '*" & [Forms]![CustomerSearchF]!txtCognome & "*'") And
([Paese] Like '*" & [Forms]![CustomerSearchF]!txtPaese & "*'") And
([Tour e Escursioni] Like '*" & [Forms]![CustomerSearchF]!txtTour_e_Escursioni & "*'") And
([Tour Operator] Like '*" & [Forms]![CustomerSearchF]!txtTour_Operator & "*'") And
([Cadice Operatore] Like '*" & [Forms]![CustomerSearchF]!txtCadice_Operatore & "*'")


for partial match:

([Date] Between Forms![CustomerSearchF]!txtDatadiinizio And Forms![CustomerSearchF]!txtDatadifine) And
(([name] Like '*" & [Forms]![CustomerSearchF]!txtNome & "*'") Or
([Cognome] Like '*" & [Forms]![CustomerSearchF]!txtCognome & "*'") Or
([Paese] Like '*" & [Forms]![CustomerSearchF]!txtPaese & "*'") Or
([Tour e Escursioni] Like '*" & [Forms]![CustomerSearchF]!txtTour_e_Escursioni & "*'") Or
([Tour Operator] Like '*" & [Forms]![CustomerSearchF]!txtTour_Operator & "*'") Or
([Cadice Operatore] Like '*" & [Forms]![CustomerSearchF]!txtCadice_Operatore & "*'"))

hi,
i would like the second option (partial match
do you ask me to do this in a query? (without making a report)
and kindly let me know, where should I put these codes? (select build event... VB code builder )
pls refer attached file also
thanks

 

[Dhanu]

LIKE should not be your "go to" relational operator. LIKE is a STRING operation and should only be used on text data types and only when you are searching for a partial match. It is most often used for searching names and addresses or other free form text blobs. Keep in mind that in most RDBMS', LIKE will force a full table scan and that means that the larger your table is, the slower the query will be. NEVER use LIKE if you have a full value for example, if you picked something from a combo or list box, those controls always return the full value.

thanks for the advice. noted

 

[Dhanu]

The best way to do this is not with a query, but with a report. You build a report based on your table, then you open the report via DoCmd.OpenReport (https://msdn.microsoft.com/en-us/vba/access-vba/articles/docmd-openreport-method-access). That allows you to pass a filter to the report. You build that filter based on your input form. the user clicks the button and gets the report with just the data they want.

hi, thanks for the idea. i need a calculation fields also. these are already attached to query. can i do the same calculation in a report also? because i need to print the final result

calculated query details : (which already exist)

(I did these calculated in a query because there is no option to save these calculated fields in my table.)

Adulti_Totale: [Adulto_€]*[adulti]
Ragazzi_Totale: [Ragazzo_€]*[ragazzi]
Bambini_Totale: [Bambino_€]*[bambini]
Quota_Totale: [Adulti_Totale]+[Ragazzi_Totale]+[Bambini_Totale]
Netto: ([Adulti_Totale]+[Ragazzi_Totale]+[Bambini_Totale])*0,75(this commision depends according to agent Eg : 25%,20%,0%)

 

[arnelgp]

you put that on you Query or whatever code you have in Run Query button.

 

[Dhanu]

you put that on you Query or whatever code you have in Run Query button.

I went to customersearchF table in design mode and selected RUN QUERY button.
and selected [Event Procedure] and pasted your codes. I got an error.

maybe i dint undertand well.
refer attachment.

can you please tell me where should I put these codes step by step from the beginning.

 

[arnelgp]

you insert that ti your query as Criteria.
shiw us the sql of qury you have and i will insert it.

 

[Dhanu]

you insert that ti your query as Criteria.
shiw us the sql of qury you have and i will insert it.

SELECT DiscoverMessinaCustomerT.Data, DiscoverMessinaCustomerT.Nome, DiscoverMessinaCustomerT.Cognome, DiscoverMessinaCustomerT.Nave, DiscoverMessinaCustomerT.Tour_Operator, DiscoverMessinaCustomerT.Codice_Operatore, PacchettiT.Tour_e_Escursioni, PacchettiT.Codice, PacchettiT.Adulto_€, PacchettiT.Ragazzo_€, PacchettiT.Bambino_€, [Adulto_€]*[adulti] AS Adulti_Totale, [Ragazzo_€]*[ragazzi] AS Ragazzi_Totale, [Bambino_€]*[bambini] AS Bambini_Totale, [Adulti_Totale]+[Ragazzi_Totale]+[Bambini_Totale] AS Quota_Totale, ([Adulti_Totale]+[Ragazzi_Totale]+[Bambini_Totale])*0.75 AS Netto
FROM PacchettiT INNER JOIN DiscoverMessinaCustomerT ON PacchettiT.PacchettoID = DiscoverMessinaCustomerT.Tour_e_Escursioni
WHERE (((DiscoverMessinaCustomerT.Data) Between [Forms]![CustomerSearchF]![txtDatadiinizio] And [Forms]![CustomerSearchF]![txtDatadifine]));

 

[Pat Hartman]

If you want to use wild cards then modify the expressions to use LIKE instead of =

Dim strWhere as String
Dim QUOTE as String
QUOTE = """"
strWhere = "YourDateFieldName Between #" & Me.txtDatadiinizio & "# And #" & Me.txtDatadifine & "#"
If Me.txtNome & "" = "" Then
Else
    strWhere = strWhere & " AND Nome Like " & QUOTE & "*" & Me.txtNome & "*" & QUOTE
End If
If Me.txtcogNome & "" = "" Then
Else
    strWhere = strWhere & " AND cogNome Like " & QUOTE & "*" & Me.txtcogNome & "*" & QUOTE
End If
....

 

[arnelgp]

SELECT DiscoverMessinaCustomerT.Data, DiscoverMessinaCustomerT.Nome, DiscoverMessinaCustomerT.Cognome, DiscoverMessinaCustomerT.Nave, DiscoverMessinaCustomerT.Tour_Operator, DiscoverMessinaCustomerT.Codice_Operatore, PacchettiT.Tour_e_Escursioni, PacchettiT.Codice, PacchettiT.Adulto_€, PacchettiT.Ragazzo_€, PacchettiT.Bambino_€, [Adulto_€]*[adulti] AS Adulti_Totale, [Ragazzo_€]*[ragazzi] AS Ragazzi_Totale, [Bambino_€]*[bambini] AS Bambini_Totale, [Adulti_Totale]+[Ragazzi_Totale]+[Bambini_Totale] AS Quota_Totale, ([Adulti_Totale]+[Ragazzi_Totale]+[Bambini_Totale])*0.75 AS Netto
FROM PacchettiT INNER JOIN DiscoverMessinaCustomerT ON PacchettiT.PacchettoID = DiscoverMessinaCustomerT.Tour_e_Escursioni
WHERE
(DiscoverMessinaCustomerT.Data Between Forms![CustomerSearchF]!txtDatadiinizio And Forms![CustomerSearchF]!txtDatadifine) And
(([Nome] Like '*" & [Forms]![CustomerSearchF]!txtNome & "*'") Or
([Cognome] Like '*" & [Forms]![CustomerSearchF]!txtCognome & "*'") Or
([Nave] Like '*" & [Forms]![CustomerSearchF]!txtPaese & "*'") Or
([Tour_Operator] Like '*" & [Forms]![CustomerSearchF]!txtTour_Operator & "*'") Or
([Cadice_Operatore] Like '*" & [Forms]![CustomerSearchF]!txtCadice_Operatore & "*'")) Or
([Tour_e_Escursioni] Like '*" & [Forms]![CustomerSearchF]!txtTour_e_Escursioni & "*'");


***
substitute the correct textboxname from your form to the Criteria Clause.

 

[Dhanu]

SELECT DiscoverMessinaCustomerT.Data, DiscoverMessinaCustomerT.Nome, DiscoverMessinaCustomerT.Cognome, DiscoverMessinaCustomerT.Nave, DiscoverMessinaCustomerT.Tour_Operator, DiscoverMessinaCustomerT.Codice_Operatore, PacchettiT.Tour_e_Escursioni, PacchettiT.Codice, PacchettiT.Adulto_€, PacchettiT.Ragazzo_€, PacchettiT.Bambino_€, [Adulto_€]*[adulti] AS Adulti_Totale, [Ragazzo_€]*[ragazzi] AS Ragazzi_Totale, [Bambino_€]*[bambini] AS Bambini_Totale, [Adulti_Totale]+[Ragazzi_Totale]+[Bambini_Totale] AS Quota_Totale, ([Adulti_Totale]+[Ragazzi_Totale]+[Bambini_Totale])*0.75 AS Netto
FROM PacchettiT INNER JOIN DiscoverMessinaCustomerT ON PacchettiT.PacchettoID = DiscoverMessinaCustomerT.Tour_e_Escursioni
WHERE
(DiscoverMessinaCustomerT.Data Between Forms![CustomerSearchF]!txtDatadiinizio And Forms![CustomerSearchF]!txtDatadifine) And
(([Nome] Like '*" & [Forms]![CustomerSearchF]!txtNome & "*'") Or
([Cognome] Like '*" & [Forms]![CustomerSearchF]!txtCognome & "*'") Or
([Nave] Like '*" & [Forms]![CustomerSearchF]!txtPaese & "*'") Or
([Tour_Operator] Like '*" & [Forms]![CustomerSearchF]!txtTour_Operator & "*'") Or
([Cadice_Operatore] Like '*" & [Forms]![CustomerSearchF]!txtCadice_Operatore & "*'")) Or
([Tour_e_Escursioni] Like '*" & [Forms]![CustomerSearchF]!txtTour_e_Escursioni & "*'");


***
substitute the correct textboxname from your form to the Criteria Clause.

I pasted above codes in SQL mode,
there was an error. pls refer the attachment
thanks.

 

[arnelgp]

replace these
'*"

with
"'*"

 

[Dhanu]

replace these
'*"

with
"'*"

hi
still, there is an error message, refer attached JPEG

 

[isladogs]

You have a )) on the penultimate line

 

[arnelgp]

SELECT DiscoverMessinaCustomerT.Data, DiscoverMessinaCustomerT.Nome, DiscoverMessinaCustomerT.Cognome, DiscoverMessinaCustomerT.Nave, DiscoverMessinaCustomerT.Tour_Operator, DiscoverMessinaCustomerT.Codice_Operatore, PacchettiT.Tour_e_Escursioni, PacchettiT.Codice, PacchettiT.Adulto_€, PacchettiT.Ragazzo_€, PacchettiT.Bambino_€, [Adulto_€]*[adulti] AS Adulti_Totale, [Ragazzo_€]*[ragazzi] AS Ragazzi_Totale, [Bambino_€]*[bambini] AS Bambini_Totale, [Adulti_Totale]+[Ragazzi_Totale]+[Bambini_Totale] AS Quota_Totale, ([Adulti_Totale]+[Ragazzi_Totale]+[Bambini_Totale])*0.75 AS Netto
FROM PacchettiT INNER JOIN DiscoverMessinaCustomerT ON PacchettiT.PacchettoID = DiscoverMessinaCustomerT.Tour_e_Escursioni
WHERE
(DiscoverMessinaCustomerT.Data Between Forms![CustomerSearchF]!txtDatadiinizio And Forms![CustomerSearchF]!txtDatadifine) And
(([Nome] Like "*" & [Forms]![CustomerSearchF]!txtNome & "*") Or
([Cognome] Like "*" & [Forms]![CustomerSearchF]!txtCognome & "*") Or
([Nave] Like "*" & [Forms]![CustomerSearchF]!txtPaese & "*") Or
([Tour_Operator] Like "*" & [Forms]![CustomerSearchF]!txtTour_Operator & "*") Or
([Cadice_Operatore] Like "*" & [Forms]![CustomerSearchF]!txtCadice_Operatore & "*")) Or
([Tour_e_Escursioni] Like "*" & [Forms]![CustomerSearchF]!txtTour_e_Escursioni & "*");


***
substitute the correct textboxname from your form to the Criteria Clause.

 

[isladogs]

I can't see what changes Ariel has made at a quick glance.

However I can see there are still 2 ) brackets on the penultimate line and only one ) on the last line.

Should be the other way around.