UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Editing Expressions With Custom Date, Access 2013 Web App    
 
   
CaptElmo2165
post Nov 21 2017, 04:56 AM
Post#1



Posts: 76
Joined: 22-October 15



Hi Folks!

I've finally made the move over to Rob's company for our Web App and it's working great (thanks for the save Rob!). So we're now using Access 2016 Web Apps for on prem.

I've always been bugged by the limited date formatting in AWA and wanted to move from the standard options to "dd MMM yyyy". Since you can't do this for standard text boxes in table views, I found you can use the format() function in a query to build an expression and merrily set off building displayqueries for my tables which hold all the table's fields plus an expression for each date field in the table... only to find that none of these are editable in a view because they're read only! frown.gif

Is there any way to make these expressions editable?

Cap
Go to the top of the page
 
RobKoelmans
post Nov 21 2017, 06:04 AM
Post#2



Posts: 434
Joined: 25-November 14
From: Groningen, Netherlands


Hi Capt,
Thanks for this. Still not sure whom to connect you to, though. Are you Aussie or UK? Clearly not Dutch smile.gif.

Hope you'll be able to look at the webinar tonight at seven US Eastern time on AccessUserGroups.org (AWA section). There's connectivity with PowerApps/Flow and with MS-Access Desktop functionality in there.
It'll be recorded as well. You're end users can instigate anything within your Access Desktop Applications from within their AWA's and PowerApps/Flow).

There are ways of using format() more effectively. The one I can thing of is to have a calculated field in which you use the proper display format in the table (and another column to maintain the date in). But you can use it in field controls as well, if I recall right.

See remarks in this link if you hadn't seen this before. https://msdn.microsoft.com/en-us/library/of...e/jj249282.aspx

There's also links in there to the proper syntaxes like https://docs.microsoft.com/en-us/dotnet/sta...-format-strings
Everything from Format() in C# applies to Format() in AWA.

This is the link from George to the webinar: https://meet.lync.com/itimpactinctemp/aug/JKP3TTG7

Kind regards,
Rob
Go to the top of the page
 
CaptElmo2165
post Nov 21 2017, 06:49 AM
Post#3



Posts: 76
Joined: 22-October 15



Hi Rob,

It's Nathan - UK (I didn't want to both Gerard again, poor guy must be sick of hearing from me over the past couple of weeks!).
Cheers for the reply. Sadly, it's a bit late for me tonight but I'll definitely be catching up on it tomorrow.

Is your fist suggestion to create an additional field in the table and make this a calculated field with a Format() expression to reformat an adjacent field in the same table? Maintaining the date in the original field and reformatting for display in the calculated field? If so, this doesn't seem to work as format() isn't allowed at the table level in calculated fields. Is it possible to use DateFromParts function to reformat? I've had a stab but can't see how to do that. I'm going to have a look through Jeff C's book for hints and a read through your second link.

Cap
Go to the top of the page
 
GroverParkGeorge
post Nov 21 2017, 06:52 AM
Post#4


UA Admin
Posts: 31,263
Joined: 20-June 02
From: Newcastle, WA


Please do join us tonight if you can. It's at 7:00pm Eastern (US), which means it's pretty late for the UK. Note that it usually takes several days for the recording to be processed and posted, I'll let everyone know when it's ready.

I've used the solution Rob proposes for controls where I wanted to display a formatted date on the page but store the unformatted version for editing. When users invoke edit mode, the fields display themselves appropriately, which helps guide users in making their edits to the unformatted control.

--------------------
Go to the top of the page
 
RobKoelmans
post Nov 21 2017, 07:34 AM
Post#5



Posts: 434
Joined: 25-November 14
From: Groningen, Netherlands


Hi Cap,
I should have checked whether Format() exists in calculated field expressions. Like George said, there are ways but what came up in me is not a way. You'd have to build it up with a concatenate or something. In hindsight, it's obvious why format() is at presentation level in the form controls. Sorry about that.

Don't worry about startup issues. We caused parts of it ourselves by going for x509 certificates. We're working on migrating secure cookies (and storing them automatically by parsing secure keys with URL's in e-mails) from our SharePoint 2013 environment. Just as with configuring Access Web Services on SharePoint 2016, migrating Login Redirection and Form Based Authentication from SharePoint2013 is far from trivial. You'd expect to just re-publish your Visual Studio project to another server or farm, but each square inch of progress has to be battled for. Also because several changes and updates in the 2013 environment weren't properly documented by us, unfortunately.

A TMG firewall only accepts certificates of its own domain and ignores forests and domain-trusts as far as we can tell. So we have to create external accounts twice to get you a certificate. Giving you rights on your sitecollection, we can't distinguish between both your names because we gave them the same friendly name frown.gif. That gave you and us some misunderstanding.

We're constantly learning and - in a couple of weeks - you'll have two pass authentication with secure cookies that won't ask for anything for 30 days when you start the AWA.
Kind regards,
Rob
Go to the top of the page
 
CaptElmo2165
post Nov 21 2017, 07:45 AM
Post#6



Posts: 76
Joined: 22-October 15



Hi Both,

Funnily enough, I went to Concat with DatePart next but whilst this also works for display, it's using a calculated field as the source so it's also read-only. I tried changing the original date fields form control default value to a Concat with DateParts to try and elicit the behaviour George describes, where it defaults to the std format on edit, but that doesn't work either. hmmm...

Webinar-wise, I'll have to see how bleary-eyed I get by that point.

Transition-wise, I can only imagine what you guys have to contend with on your end Rob. Moving stuff on m side (which is piece of cake by comparison) has been enough to get my head around. I guess this is the issue with letting mere mortals like me attempt development heh!

Cap
Go to the top of the page
 
CaptElmo2165
post Nov 21 2017, 07:52 AM
Post#7



Posts: 76
Joined: 22-October 15



Ooo hang about!

If I make the control source of the textbox a Concat with DateParts this works as George describes. So, the only thing I now need to do is convert my month integers to MMM e.g. Apr instead of 4...

Cap
Go to the top of the page
 
CaptElmo2165
post Nov 21 2017, 09:12 AM
Post#8



Posts: 76
Joined: 22-October 15



{sigh} I take that back. It doesn't pop up and tell me it's read-only but it equally won't let me edit the field if i use the above method. Not quite sure how GPG got this working. I'm also baffled as to why the only good universal date std (short and clear enough for both US and EU people understand) isn't included as standard. I'll keep chasing a solution and post if I find something.

Cap
Go to the top of the page
 
GroverParkGeorge
post Nov 21 2017, 10:09 AM
Post#9


UA Admin
Posts: 31,263
Joined: 20-June 02
From: Newcastle, WA


Okay, I'll go see what I did, but I've stopped working on these things for a while.

--------------------
Go to the top of the page
 
CaptElmo2165
post Nov 21 2017, 11:41 AM
Post#10



Posts: 76
Joined: 22-October 15



Thanks George! Much appreciated.
Go to the top of the page
 
GroverParkGeorge
post Nov 21 2017, 01:52 PM
Post#11


UA Admin
Posts: 31,263
Joined: 20-June 02
From: Newcastle, WA


We spoke too soon.

I was remembering badly. It was a phone number field, not a data number field, for which I created and saved a "formatted" version for display. Since it was already a string (short text) it was relatively easy to manipulate.

My thinking now is that you'd need to do this in a query. Here's a sample of a calculated field that would show a date as Month Day, Year, e.g. November 22, 2017. This works in an AWA for my client.

One of the fields on the view would be bound to FirstContactDate for editing purposes and the other too FirstContactDisplay for display purposes. Anywhere you DO need to edit the date, you can put both fields on a form, but if the form is needed for display purposes only, you could include on the display version.

FirstContactDisplay: Format([FirstContactDate],"m")+", "+Format([FirstContactDate],"yyyy")

--------------------
Go to the top of the page
 
CaptElmo2165
post Nov 22 2017, 04:49 AM
Post#12



Posts: 76
Joined: 22-October 15



Hey George,

Thanks for the reply. Yeh, I got the query method to work with an expression formatting the editable field, but as you say the expression itself isn't editable. Is there a nice way to hide the fact that there are actually two fields on the form without actually displaying both, which is a bit messy? I'm thinking my best option might be to lay it out as follows:

|__display field__| |__| << edit field

i.e. make the edit field small and place it right next to the display field, but make it hidden normally. Then, make a custom Edit button for the action bar which un-hides the edit field, and hides the display field to avoid confusion. At least here, the user can just use the date selector for the edit field. Space and layout is my issue in most places. That, and having to create a display query for each form... frown.gif

Thank you both for checking this all out for me!

Cap
Go to the top of the page
 
GroverParkGeorge
post Nov 22 2017, 07:01 AM
Post#13


UA Admin
Posts: 31,263
Joined: 20-June 02
From: Newcastle, WA


Unfortunately, that's about the best I've figured out myself.

--------------------
Go to the top of the page
 
CaptElmo2165
post Nov 22 2017, 07:41 AM
Post#14



Posts: 76
Joined: 22-October 15



OK great!

I've had to add new Add / Edit / Cancel / Save buttons to each page to hide and unhide those controls as necessary which makes this a long-winded fix but it looks good and works too!

Thanks again both!

Cap
Go to the top of the page
 
GroverParkGeorge
post Nov 22 2017, 08:46 AM
Post#15


UA Admin
Posts: 31,263
Joined: 20-June 02
From: Newcastle, WA


Pretty much everything in AWAs has been "...a long-winded fix " it seems. thumbup.gif

--------------------
Go to the top of the page
 
RobKoelmans
post Nov 23 2017, 02:57 PM
Post#16



Posts: 434
Joined: 25-November 14
From: Groningen, Netherlands


Nice to know you got it working.
Perhaps having read/edit/create modes in your forms is going to benefit for more than just this in the future. We recently chose to implement them in all of our PowerApps screens as well. They make life a lot easier to programmers and provide for a professional user experience imo.

Rob
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 03:29 PM