Results 1 to 5 of 5
  1. #1
    RudiN is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Location
    Australia
    Posts
    3

    Need to append short text to long text field update query ms access 2016

    I would like to update an access schedule to Google Calendar. I have created an ODBC database link to the calendar and have a build a query to append to the calendar.
    My problem is that I have short text fields and the fields I am updating in Google Calendar are long text fields, so I am getting a type conversion failure.


    I would appreciate suggestions on how can I get around this problem?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    suggest provide some example data and the code you are using - you can expect issues trying to convert long text to short text but not the other way round

  3. #3
    RudiN is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Location
    Australia
    Posts
    3
    Are you saying the query should append a short text to a long text field?

    Here is the query SQL, the google link table design (part of) and a table I created to look at the field structure which is also attached, by temporarily changing the query to a make table query.

    INSERT INTO GoogleCalendar_Trainers ( CalendarID, Summary, Description, location, AllDayEvent, StartDate, EndDate, Kind, Status, Updated, ColorID, CreatorEmail, OrganizerEmail, OrganizerDisplayName )
    SELECT "mdgbarlio3fejknb0fgkeusccl@group.calendar.google. com" AS CalendarID, [CourseScheduleUnits]![UnitCode] & " " & [CourseScheduleUnits]![Trainer] & " " & [CourseScheduleUnits]![Room] AS Summary, [CourseScheduleUnits]![startDate] & " " & [CourseScheduleUnits]![trainingtype] AS Description, [CourseScheduleUnits]![CityCode] AS location, "True" AS AllDayEvent, CourseScheduleUnits.StartDate, [CourseScheduleUnits]![startdate]+1 AS EndDate, "Calendar#event" AS Kind, "Confirmed" AS Status, Now() AS Updated, IIf([CourseScheduleUnits]![TrainingType]="Workshop",5,IIf([coursescheduleunits]![citycode]="MAROO",2,1)) AS ColorID, "info@daisylearning.com.au" AS CreatorEmail, "mdgbarlio3fejknb0fgkeusbbk@group.calendar.google. com" AS OrganizerEmail, "Trainers" AS OrganizerDisplayName
    FROM CourseScheduleUnits
    WHERE (((CourseScheduleUnits.StartDate)=[Enter Course Start Date]) AND ((CourseScheduleUnits.TrainingType)="Full" Or (CourseScheduleUnits.TrainingType)="Workshop" Or (CourseScheduleUnits.TrainingType)="Group"));
    Attached Thumbnails Attached Thumbnails GoogleCalendarTable.jpg   ScheduletoGoogle.jpg  

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Are you saying the query should append a short text to a long text field?
    yes - but in your example you are not using the field, you are using a hardcoded value (with a space in it).

    Why do you think this field is the problem? - I would have thought this was more likely

    "True" AS AllDayEvent

    In your destination table, AllDayEvent is a boolean which is numeric

  5. #5
    RudiN is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Location
    Australia
    Posts
    3
    I should have spotted that. Can't see the forest for the trees. Thank you, thank you.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 24
    Last Post: 03-12-2019, 08:02 PM
  2. Replies: 3
    Last Post: 11-14-2018, 01:20 PM
  3. Replies: 1
    Last Post: 05-18-2016, 09:46 AM
  4. UPDATE QUERY to Separate Numbers from Text in a Text Field
    By pjordan@drcog.org in forum Queries
    Replies: 2
    Last Post: 05-29-2015, 02:44 PM
  5. Long Text field in an Update query
    By Dave D in forum Queries
    Replies: 2
    Last Post: 08-03-2014, 12:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums