make a query some particular fields (1 Viewer)

[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.

guys, something wrong somewhere . pls refer attachments

and i have a error message : enter parameter value ?

in query some fields are duplicated ( there are two columns )

i have a dout about this code , [Nave] Like "*" & [Forms]![CustomerSearchF]!txtPaese & "*") in my form i done have nave text box

not Cadice_Operatore should be Codice_Operatore

thanks

 

[Dhanu]

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
....

thanks for the advice. currently im following arnelgp steps. because I don't understand advance access techniques. I have only two months experience about access.

 

[arnelgp]

it meabs my form variable is incorrect, substitute with correct one. else you may post your db for the last time.

 

[Dhanu]

it meabs my form variable is incorrect, substitute with correct one. else you may post your db for the last time.

hi i attached my data base

thanks

 

[Dhanu]

it meabs my form variable is incorrect, substitute with correct one. else you may post yo
ur db for the last time.

i deleted my old customersearchform and my customersearchquerytable . and pasted your codes in new query SQL form. there also an error. pls refer attach JPEG

i think according to my relationship with two tables (refer 11 JPEG) there are two Tour_e_Escursioni

 

[isladogs]

I haven't looked at your db but the error shown in your first screenshot is easily solved. You just need to specify one of the 2 tables for that field in your query.

Also just to point out for the third time about the )) bracket error which you haven't corrected

 

[Dhanu]

I haven't looked at your db but the error shown in your first screenshot is easily solved. You just need to specify one of the 2 tables for that field in your query.

Also just to point out for the third time about the )) bracket error which you haven't corrected

can you pls modify the correct codes for me.
thanks. so i can create a brand new query instead of modifying the old one

and i modified the bracket error also after that i got an error.


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 & "*");

 

[Dhanu]

I haven't looked at your db but the error shown in your first screenshot is easily solved. You just need to specify one of the 2 tables for that field in your query.

Also just to point out for the third time about the )) bracket error which you haven't corrected

main table contains :

ClienteID
Data
Nome
Cognome
Paese
Lingua
Nave
Durata
Pick_Up
Orario
Codice_Operatore
Telefono
Posta_Electtronica
Tour_Operator
Tour_e_Escursioni
Adulti
Ragazzi
Bambini
Adulti_Totale
Ragazzi_Totale
Bambini_Totale
Quota_Totale
Commissioni
Commissione Euro
Netto
Note

secondry table cointains :

PacchettoID
Tour_e_Escursioni
Adulto_€
Ragazzo_€
Bambino_€
Codice

what I wanted is, when user selects Tour_e_Escursioni in data entry form, i want automatically fill Adulto_€,Ragazzo_€,Bambino_€,Codice fields. so i choose this relationship between tables.

thanks

 

[isladogs]

Hi dhanu

Sorry I haven't got time to look at your db today & anyway I don't want to 'tread on arnel's toes'

So the following may or may not be correct
Changes made (IN RED):
1. I've added the table name on the last line
2. I've corrected the bracketing on the last 2 lines

If you still get errors, it will need to wait for arnel or someone else to look at your db

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 & "*"[COLOR="red"])[/COLOR] Or 
([COLOR="Red"][PacchettiT.].[/COLOR][Tour_e_Escursioni] Like "*" & [Forms]![CustomerSearchF]!txtTour_e_Escursioni & "*"[COLOR="red"]))[/COLOR];

Good luck

 

[Dhanu]

Hi dhanu

Sorry I haven't got time to look at your db today & anyway I don't want to 'tread on arnel's toes'

So the following may or may not be correct
Changes made (IN RED):
1. I've added the table name on the last line
2. I've corrected the bracketing on the last 2 lines

If you still get errors, it will need to wait for arnel or someone else to look at your db

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 & "*"[COLOR="red"])[/COLOR] Or 
([COLOR="Red"][PacchettiT.].[/COLOR][Tour_e_Escursioni] Like "*" & [Forms]![CustomerSearchF]!txtTour_e_Escursioni & "*"[COLOR="red"]))[/COLOR];

Good luck

dear sir,

i tried the codes given you. there is an error. pls refer attached JPEG.

and thank you very much for looking on this matter.

 

[isladogs]

Apologies - extra . crept in by mistake

Try again:

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 
([COLOR="Red"][PacchettiT][/COLOR].[Tour_e_Escursioni] Like "*" & [Forms]![CustomerSearchF]!txtTour_e_Escursioni & "*"));

Hopefully its OK now but if not, see previous comments

 

[arnelgp]

here try this

 

[Dhanu]

Apologies - extra . crept in by mistake

Try again:

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 
([COLOR="Red"][PacchettiT][/COLOR].[Tour_e_Escursioni] Like "*" & [Forms]![CustomerSearchF]!txtTour_e_Escursioni & "*"));

Hopefully its OK now but if not, see previous comments

yes, it seems ok now. but we have another problem now. what is enter parameter value? pls refer JPEG
thanks.

 

[Dhanu]

here try this

hi,,

thank you very much. i tryied to run query using search by name. it runs but it dosent show me the results.only empty query.

i attached a printscreen too.

thanks.

 

[arnelgp]

i hope this is the final

 

[Dhanu]

i hope this is the final

hi arnegp,

sorry to bother you always.

query form only works with date. other criteria no. it gives me only empty query result.

thanks

 

[Pat Hartman]

thanks for the advice. currently im following arnelgp steps. because I don't understand advance access techniques. I have only two months experience about access.

Whether you are using code or a query, you still need to understand what is going on and you need to be able to help with finding syntax errors since you are the one with the database. It is very difficult to get complex SQL correct unless you are actually working in a database. If you post a database with your tables and the query (disguise any sensitive data), the others will have much less trouble getting this syntax correct.

 

[Dhanu]

Dear all, thank you very much for helping me on this matter. I thought to describe you all my needful to understand better.
I’m working for a tourism company. So, we need a database to keep track our customers.
I want a table which includes customer all details. ClienteID , Data (date) , Nome (name) , Cognome (sure name ), Paese (country) , Lingua(language) , Nave , Durata (duration) , Pick_Up, Orario (time) , Codice_Operatore (operator code), Telefono (phone no), Posta_Electtronica (email) , Tour_Operator , Tour_e_Escursioni (Tour and Excursions), Adulti (adults), Ragazzi(teenagers), Bambini (kids) , Adulti_Totale ( adults total ) , Ragazzi_Totale (teenagers total) , Bambini_Totale ( kids total ), Quota_Totale ( total price) , Commissioni (commission ) , Commissione Euro ( commission euro) , Netto (net total ) , Note
I created a form to enter above details to the database. Which includes calculated Fields also. But you people advised me to remove these felids, so I attached them to a query.
What I wanted is, when the user clicks on Tour_e_Escursioni field, auto fill Adulto_€, Ragazzo_€, Bambino_€, Codice (code related to package) fields. so, I created another table and selected looked up wizard on Tour_e_Escursioni field on table one. (Picture 03)
Now I want these things to do from my table,
01. I want to save calculated detail somewhere because we need to run report end of the month. (calculated fields are - Adulti_Totale, Ragazzi_Totale, Bambini_Totale, Quota_Totale, Netto.)
Note: I need to apply a commission also. commission will be 25%, 20%, 0%

E.g.: Tour_e_Escursioni: ALCANTARA_TAORMINA package ,

Adulto euro 20 participants 02 total 40
Ragazzo euro 15 participants 02 total 30
Bambino euro 00 participants 01 total 00
Quota_Totale 70
Comissione 20%
Commissione euro 14 euro
Netto 56 euro
(Pls refer picture 01)



02. I need to make a mask (form) to run a report or query (if report is more better because I need to print) it should be including these criterias to search options - Data di inizio (start date), Data di fine (end date), Nome (name), Cognome (sure name), Paese (country), Tour e Escursioni (Tour and Excursions), Tour Operator, Codice Operatore (operator code)
(picture 02)

I think you all got an idea now. above all steps, I did from watching YouTube tutorials. I don’t have any further knowledge about access.
Thank you.

 

[arnelgp]

finally? is over.

 

[isladogs]

finally? is over.

No I don't think so....

Dhanu appears to have 2 new questions as a follow up ... or have you also answered those?