SQL string Syntaxerror(Missing Operator) (1 Viewer)

[silentwolf]

Hi guys,

just wondering if someone could be so kind and let me know where this Syntaxerror (missing Operator) is in that SQL string?

I checked for Typos and it seams to be ok so I guess there is something else causing the Error.

SELECT tbl_ArtikelStamm.ArtS_Bezeichnung, tbl_ArtStammPreisliste.ArtSPreis_PreisNetto, tbl_ArtikelGruppenRabatt.ArtGRab_Rabatt,
             tbl_ArtStammPreisliste.ArtSPreis_PreisNetto * (1- tbl_ArtikelGruppenRabatt.ArtGRab_Rabatt) AS NettoRabattiert
FROM tbl_Kontakte
         INNER JOIN tbl_Preislisten ON tbl_Kontakte.Kont_PreisL_IDRef=tbl_Preislisten.PreisL_ID
         INNER JOIN tbl_ArtStammPreisliste ON tbl_Preislisten.ArtSPreis_PreisL_IDRef=tbl_ArtStammPreisliste.PreisL_ID
         INNER JOIN tbl_ArtikelStamm ON tbl_ArtStammPreisliste.ArtSPreis_ArtS_IDRef=tbl_ArtikelStamm.ArtS_ID
             INNER JOIN tbl_ArtikelGruppenRabatt ON tbl_ArtikelGruppenRabatt.ArtGRab_Kont_IDRef=tbl_Kontakte.Kont_ID
             INNER JOIN tbl_ArtikelGruppe ON tbl_ArtikelGruppe.ArtG_ID=tbl_ArtikelGruppenRabatt.ArtGRab_ArtG_IDRef
WHERE tbl_Kontakte.Kont_ID= 123 AND tbl_ArtikelStamm.ArtS_ArtG_IDRef=tbl_ArtikelGruppe.ArtG_ID;

Or is it only a typo that I can't seam to find?

Sorry not that fit with SQL and hoping for some help.

Many thanks for your time!

 

[MajP]

I know that SQL does not care about some spaces but no spaces between = anywhere. I think those are needed.

tbl_Kontakte.Kont_ID= 123 tbl_Kontakte.Kont_PreisL_IDRef=tbl_Preislisten.PreisL_ID

 

[MajP]

Actually just tested. Seems SQL is pretty forgiving you can put none, one, or both and still works. By default a join gets two spaces, but a literal gets none. I would still put in the spaces before and after.

 

[arnelgp]

bring it to the SQL Design.
Access will take care of all those (()).

 

[silentwolf]

Hi MajP,

many thanks for your reply!
Unfortunatelly it is not fixing the issue (

When I run the the SQL string it highlights

SELECT tbl_ArtikelStamm.ArtS_Bezeichnung, tbl_ArtStammPreisliste.ArtSPreis_PreisNetto, tbl_ArtikelGruppenRabatt.ArtGRab_Rabatt,

SPreis_P

Is that showing where the issue is or is it just random where it markes the "Statement"?

 

[silentwolf]

Hi,
the issue is that a friend helped me with the SQL String but as he helped me already quite bit I was hoping to get someone to be able to point the issue out. And because I have no idea how it suppose to be I can not really use the SQL Design.

If that makes any sence?

 

[silentwolf]

Thanks MajP,
for clarifying!
Will look further into it...

 

[MajP]

I formatted a little more, but I do not see anything. The normal culprits are a missed comma, mismatched (), inadvertent space in name. Your commas look good, () look good, no weird spaces. I reformatted some more for visibility

SELECT tbl_artikelstamm.arts_bezeichnung,
       tbl_artstammpreisliste.artspreis_preisnetto,
       tbl_artikelgruppenrabatt.artgrab_rabatt,
       tbl_artstammpreisliste.artspreis_preisnetto * ( 1 -
       tbl_artikelgruppenrabatt.artgrab_rabatt ) AS NettoRabattiert
FROM   tbl_kontakte
       INNER JOIN tbl_preislisten
               ON tbl_kontakte.kont_preisl_idref = tbl_preislisten.preisl_id
       INNER JOIN tbl_artstammpreisliste
               ON tbl_preislisten.artspreis_preisl_idref =
                  tbl_artstammpreisliste.preisl_id
       INNER JOIN tbl_artikelstamm
               ON tbl_artstammpreisliste.artspreis_arts_idref =
                  tbl_artikelstamm.arts_id
       INNER JOIN tbl_artikelgruppenrabatt
               ON tbl_artikelgruppenrabatt.artgrab_kont_idref =
                  tbl_kontakte.kont_id
       INNER JOIN tbl_artikelgruppe
               ON tbl_artikelgruppe.artg_id =
                  tbl_artikelgruppenrabatt.artgrab_artg_idref
WHERE  tbl_kontakte.kont_id = 123
       AND tbl_artikelstamm.arts_artg_idref = tbl_artikelgruppe.artg_id

If it is highlighting the Select to from portion. I would just retype it the field names in just the select portion. Maybe some weird hidden character. May try just copying the above and repasting. Maybe will clear something up.

 

[silentwolf]

Thank you,
well then there must be a typo somewhere, I will look closer.

Many thanks for your help!

Cheers )

 

[arnelgp]

I have no idea how it suppose to be I can not really use the SQL Design

copy your sql and paste in SQL view of query designer.

 

[arnelgp]

Select to from portion.

access will write the necessary bracketing for you.

 

[The_Doc_Man]

Bring the SQL into NOTEPAD and see if any oddball characters or unexpected line breaks show up.

 

[silentwolf]

Hi guys,
thanks for your reply!

I will have a look if I can manage that..
Yes I did put the string into the SQL View... I must of missunderstood.
Will have a better look at the tables again and check if there is somewhere something I can spot.
Thanks for all the help to all of you!!

 

[MajP]

I can not really use the SQL Design

You should be able to build this in the designer

Select your fields and make your joins
1. Add a calculated field
NettoRabattiert: [tbl_artstammpreisliste].[artspreis_preisnetto] * (1 - [tbl_artikelgruppenrabatt].[artgrab_rabatt])

2. In column tbl_kontakte.kont_id
Put in the WHERE line: 123
if that is not really a number but a string "123"

3. in column tbl_artikelstamm.arts_artg_idref
Put in the where line [tbl_artikelgruppe].[artg_id]

 

[silentwolf]

Thanks for your help MajP,

No more Error but the outcome is not right... NettoRabattiert shows -120 from a Discount of 25 Percent of 5,00 pff

ArtSPreis_PreisNetto=5,00; ArtGRab_Rabatt = 25; NettoRaattiert =-120,00

I guess he assumend that the ArtGRab_Rabatt is in a negativ Number. -25
but in fact I just have 25

So this field has to be changed I guess

1. Add a calculated field
NettoRabattiert: [tbl_artstammpreisliste].[artspreis_preisnetto] * (1 - [tbl_artikelgruppenrabatt].[artgrab_rabatt])

 

[silentwolf]

Hey,

this is now working thanks to you guys and especially MajP!!
However there is still one more slight problem with this.

As there are some "Articels" with no "Rabatt" which join needs to change so it shows me all the "Articels"
Can this be done within one Query...?





Cheers.

 

[MajP]

In the design view you can change an inner join to and outerjoin. Click on the connector and it should bring up a property window. It should give you the option to show all articles and only rabatt where equal.

Notice the arrow going right for selection 2. It would go left for selection 3, and there would be no arrow for 1.

 

[silentwolf]

Well I do know about the joins you show here but when I do change to join from tbl_ArtikelGruppe ("Group") ArtG_ID and tbl_ArtikelStamm("Artikels") so it would show all records in tbl_ArtikelStamm I get an Error it has to many outerjoins...
So do I need to change my original query to be able to change my join..

 

[MajP]

can you do a screen capture like I did showing your joins. I have never seen a "too many" error. You can get an ambigous outer join which can be fixed if you ensure your joins "flow" into a single direction.

If you are having troubel you could include all of the other tables and fields in a first join. Then use that query in a second query including the tbl_artikelgruppenrabatt

 

[silentwolf]

Hi,
thanks for taking the time and helping me getting through this! It means alot as I am working for quite some time on that one.

Yes in theory I know all this but when it comes down to actually doing it with this query I keep getting errors or wrong amount of recordsets.

This is the query at the moment...