setting entered not valid for this entry error

bwlytkr

Board Regular
Joined
Jun 8, 2012
Messages
175
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Need help resolving this error. Any help would be greatly appreciated.

The setting you entered is not valid for this entry
To see valid settings for this property, search help index for the name of this property.

Here isa copy of my SQL statement

SELECT [ALMSLA NTG].Priority, [SO MSLA SURVEY Master].[SourceOne Deadline Date], [ALNWFL PXFR02 NTG Master].TicketId, [ALNWFL PXFR02 NTG Master].[PXFR MOST RECENT EVENT NAME], [ALNWFL PXFR02 NTG Master].[Responsible Party], [ALNWFL PXFR02 NTG Master].Pxfr_TaskOwner, [ALNWFL PXFR02 NTG Master].DASHBOARDPXFR, [ALNWFL PXFR02 NTG Master].[PXFR JOB NM], [ALNWFL PXFR02 NTG Master].MostRecentEvent_Remarks, [ALNWFL PXFR02 NTG Master].MostRecentEvent_Created_ts, [SE Bid Zone].[BID ZONE], [ALNWFL PXFR02 NTG Master].[NTG DAYS Age Bucket], [ALNWFL PXFR02 NTG Master].[C&E DIRECTOR], [ALNWFL PXFR02 NTG Master].[Const AM], [ALNWFL PXFR02 NTG Master].[Const Placing Network Manager], [ALNWFL PXFR02 NTG Master].[DBT AM], [ALNWFL PXFR02 NTG Master].[Pole Owner Name], [ALNWFL PXFR02 NTG Master].[PSC TICKET], [ALNWFL PXFR02 NTG Master].[SE/NON SE Ticket], [ALMSLA NTG].TicketNumber, [ALMSLA NTG].Status, [ALMSLA NTG].TicketType, [ALMSLA NTG].TicketCreator, [ALMSLA NTG].Name, [ALMSLA NTG].CreatedBy, [ALMSLA NTG].CreatedOn, [ALMSLA NTG].OwnerName, [ALMSLA NTG].PoleOwner, [ALMSLA NTG].ContactName, [ALMSLA NTG].ContactPhone, [ALMSLA NTG].ContactEmail, [ALMSLA NTG].State, [ALMSLA NTG].County, [ALMSLA NTG].Place, [ALMSLA NTG].HouseNumber, [ALMSLA NTG].StreetName, [ALMSLA NTG].CrossStreet, [ALMSLA NTG].TicketNumberOfPoles, [ALMSLA NTG].PoleNumber, [ALMSLA NTG].Latitude, [ALMSLA NTG].Longitude, [ALMSLA NTG].MiscId, [ALMSLA NTG].ReferenceId, [ALMSLA NTG].WorkRequestedDate, [ALMSLA NTG].NextToGo, [ALMSLA NTG].Remarks, [ALMSLA NTG].StepNo, [ALMSLA NTG].DaysAged, [ALMSLA NTG].Member, [ALMSLA NTG].Status1, [ALMSLA NTG].JobType, [ALMSLA NTG].StepNumberOfPoles, [ALMSLA NTG].JobIdentifier, [ALNWFL PXFR02 NTG Master].[PXFR JOB TYPE], [ALMSLA NTG].Interval, [ALMSLA NTG].ResponseRequestDate, [ALMSLA NTG].NextToGoStartDate, [ALMSLA NTG].ReopenedDate, [ALMSLA NTG].UpdatedOn, [ALMSLA NTG].MemberPoleNumber, [ALMSLA NTG].Latitude1, [ALMSLA NTG].Longitude1, [ALMSLA NTG].Location, [ALMSLA NTG].[Reference Id], [ALMSLA NTG].TaskId, [ALNWFL PXFR02 NTG Master].WC_CLLI, [ALMSLA NTG].Remarks1
FROM ((([ALNWFL PXFR02 NTG Master] INNER JOIN [ALMSLA NTG] ON [ALNWFL PXFR02 NTG Master].[NJUNS Tkt Nbr] = [ALMSLA NTG].TicketNumber) LEFT JOIN WLS07 ON [ALNWFL PXFR02 NTG Master].WC_CLLI = WLS07.clli) LEFT JOIN [SE Bid Zone] ON [ALNWFL PXFR02 NTG Master].WC_CLLI = [SE Bid Zone].CLLI) LEFT JOIN [SO MSLA SURVEY Master] ON [ALNWFL PXFR02 NTG Master].[NJUNS Tkt Nbr] = [SO MSLA SURVEY Master].TicketNumber
WHERE ((([ALNWFL PXFR02 NTG Master].[Responsible Party]) Like "Source*") AND (([ALMSLA NTG].State) Like "LO*" Or ([ALMSLA NTG].State) Like "MI*"));
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
There's no way anyone is going to pinpoint the cause by reading that. If that sql is based on tables in your db, try opening them directly and see if that error is raised. I suspect this might involve SharePoint in some way.

Also, some suggestions here
 
Upvote 0
Without having access to your data, it would be extremely difficult for us to guess where the issue might be occurring.

Better to give you tips on how you can find it yourself.
My tip is to re-write the query, starting small, and working your way up. Whenever you find an error, start and hone in on the last step you did (because that is where the error is occurring).
1. Start off with a simple select query, only listing one table, and one field. Check the query and see if it works.
2. Add in all the other fields from the table that you want in the query. Check the query and see if it still works.
3. Add in your second table (and necessary join), and one field from that table. Check the query and see if it works.
4. Add in the rest of the fields you want from the second table in your query. Check the query and see if it works.
5. Repeat steps 3 and 4 for all your other tables.
6. Add in your first criteria (WHERE clauses). Check the query and see if it works.
7. Repeat step 6 for all your other criteria

If you build it up, bit-by-bit, like that, you should be able to identify the problem part, and then focus on that.
 
Upvote 0
Without having access to your data, it would be extremely difficult for us to guess where the issue might be occurring.

Better to give you tips on how you can find it yourself.
My tip is to re-write the query, starting small, and working your way up. Whenever you find an error, start and hone in on the last step you did (because that is where the error is occurring).
1. Start off with a simple select query, only listing one table, and one field. Check the query and see if it works.
2. Add in all the other fields from the table that you want in the query. Check the query and see if it still works.
3. Add in your second table (and necessary join), and one field from that table. Check the query and see if it works.
4. Add in the rest of the fields you want from the second table in your query. Check the query and see if it works.
5. Repeat steps 3 and 4 for all your other tables.
6. Add in your first criteria (WHERE clauses). Check the query and see if it works.
7. Repeat step 6 for all your other criteria

If you build it up, bit-by-bit, like that, you should be able to identify the problem part, and then focus on that.
Thanks for help. I’ll see what I can figure out.
 
Upvote 0
Here is your code formatted to be at least readable.
Code:
SELECT [almsla ntg].priority,
       [so msla survey master].[sourceone deadline date],
       [alnwfl pxfr02 ntg master].ticketid,
       [alnwfl pxfr02 ntg master].[pxfr most recent event name],
       [alnwfl pxfr02 ntg master].[responsible party],
       [alnwfl pxfr02 ntg master].pxfr_taskowner,
       [alnwfl pxfr02 ntg master].dashboardpxfr,
       [alnwfl pxfr02 ntg master].[pxfr job nm],
       [alnwfl pxfr02 ntg master].mostrecentevent_remarks,
       [alnwfl pxfr02 ntg master].mostrecentevent_created_ts,
       [se bid zone].[bid zone],
       [alnwfl pxfr02 ntg master].[ntg days age bucket],
       [alnwfl pxfr02 ntg master].[c&e director],
       [alnwfl pxfr02 ntg master].[const am],
       [alnwfl pxfr02 ntg master].[const placing network manager],
       [alnwfl pxfr02 ntg master].[dbt am],
       [alnwfl pxfr02 ntg master].[pole owner name],
       [alnwfl pxfr02 ntg master].[psc ticket],
       [alnwfl pxfr02 ntg master].[se/non se ticket],
       [almsla ntg].ticketnumber,
       [almsla ntg].status,
       [almsla ntg].tickettype,
       [almsla ntg].ticketcreator,
       [almsla ntg].name,
       [almsla ntg].createdby,
       [almsla ntg].createdon,
       [almsla ntg].ownername,
       [almsla ntg].poleowner,
       [almsla ntg].contactname,
       [almsla ntg].contactphone,
       [almsla ntg].contactemail,
       [almsla ntg].state,
       [almsla ntg].county,
       [almsla ntg].place,
       [almsla ntg].housenumber,
       [almsla ntg].streetname,
       [almsla ntg].crossstreet,
       [almsla ntg].ticketnumberofpoles,
       [almsla ntg].polenumber,
       [almsla ntg].latitude,
       [almsla ntg].longitude,
       [almsla ntg].miscid,
       [almsla ntg].referenceid,
       [almsla ntg].workrequesteddate,
       [almsla ntg].nexttogo,
       [almsla ntg].remarks,
       [almsla ntg].stepno,
       [almsla ntg].daysaged,
       [almsla ntg].member,
       [almsla ntg].status1,
       [almsla ntg].jobtype,
       [almsla ntg].stepnumberofpoles,
       [almsla ntg].jobidentifier,
       [alnwfl pxfr02 ntg master].[pxfr job type],
       [almsla ntg].interval,
       [almsla ntg].responserequestdate,
       [almsla ntg].nexttogostartdate,
       [almsla ntg].reopeneddate,
       [almsla ntg].updatedon,
       [almsla ntg].memberpolenumber,
       [almsla ntg].latitude1,
       [almsla ntg].longitude1,
       [almsla ntg].location,
       [almsla ntg].[reference id],
       [almsla ntg].taskid,
       [alnwfl pxfr02 ntg master].wc_clli,
       [almsla ntg].remarks1
FROM   ((([alnwfl pxfr02 ntg master]
          INNER JOIN [almsla ntg]
                  ON [alnwfl pxfr02 ntg master].[njuns tkt nbr] =
                     [almsla ntg].ticketnumber)
         LEFT JOIN wls07
                ON [alnwfl pxfr02 ntg master].wc_clli = wls07.clli)
        LEFT JOIN [se bid zone]
               ON [alnwfl pxfr02 ntg master].wc_clli = [se bid zone].clli)
       LEFT JOIN [so msla survey master]
              ON [alnwfl pxfr02 ntg master].[njuns tkt nbr] =
                 [so msla survey master].ticketnumber
WHERE  ( ( ( [alnwfl pxfr02 ntg master].[responsible party] ) LIKE "source*" )
         AND ( ( [almsla ntg].state ) LIKE "lo*"
                OR ( [almsla ntg].state ) LIKE "mi*" ) );

You are not setting anything in there, that I can see, so can only assume it is about the WHERE or JOIN fields?
 
Upvote 0

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top