MS Access Help

[Martynt74]

My dads struggling with a form on a database and given he's more capable in Access than i am i decided the best solution was to ask on here!

I've uploaded a word document where he states the issue and then the database on my onedrive which should be shared using the following link.

https://1drv.ms/f/s!AskUp4cdx3krjVL4g67eW_Dohpz-

His instructions don't read well to me so if anyone struggles i'll try and get him to clarify.

 

[gpuerrilla]

I did not get a lot out of the Word document. Can you clarify what the problem is by just typing it here?

 

[Martynt74]

Having had a bit of a play. The issue seems to be that on form “frm_prop_vales” he wants to be able to enter values into the yellow cells to update the table but they seem to be locked.

On form “frm_prop_values_updatable” which looks to be the same form the cells are unlocked and editable.

 

[gpuerrilla]

There only appears to be one record. Add a few more. When you like tables, sometimes you can complicate through rules such as enforcing integrity.

 

[gpuerrilla]

Type above should read "when you link". How did you get on Marv?

 

[Martynt74]

Just saw this, the main issue is understanding my dad!

It does seem to be with the way that he's calculating things as in the second example which doesn't work the tables aren't actually linked.

I think that he'd be better doing his calculations elsewhere and then just using the first example to show info on the form.

He's said.

This one works



"I am only showing the K values for the calculated and proposed and that means I can update the yellow cells"



This one doesn't

"If I calculate the SL% using the modified query I get to show the SL% but cant update the yellow cells"

For reference the K values he mentions are the service levels (SL) values

 

[gpuerrilla]

OK. I would try telling him to get queries to calculate values, use a good naming convention. You can then build a new query to use that calculated query. It may sound strange but basically if you are getting one query to do too much it wont work.

However if you split up the queries you can add them like the tables in the query builder and usually that overcomes some issues.

 

[SourChipmunk]

Hi everyone. Using this thread as we probably don't need a new one for what I hope is a simple question/solution.

I am trying to pull a device shortname from a list of both shortnames and FQDNs. I'm able to do it properly if the entry is FQDN, but it will return a #Func! error if it does not detect a full stop in the field.

Example:
mail.google.com will return mail
but mail will return #Func!

The expression I am currently using:

NAME: Left([DNS],InStr([DNS],".")-1)

The formula in Excel is very easy, but the iferror command doesn't exist in Access. The closest I can find is Nz(), but I can't make it work.

=IFERROR(LEFT(A1,FIND(".",A1)-1),A1)

Any ideas?

 

[gpuerrilla]

You have asked it to look for both punctuation but have not told the argument what happens if it doesnt find it. Generally you just add a comma and "" in excel, I am trying to think of the Access answer as I type this. It looks like its scanning across the text field for the full stop as it retrieves anything to the left of one. As your function is returning 0 it has nothing to present to you.

Options are wrap an If around it, or you could use the mid function just like the Instr straight after to get the latter data. You may have to add a stop to your data or have something else that it can separate with.

 

[andshrew]

You want to use an If statement to check if there is a . in the string and then do what you want with it depending on that:

IIf(«expression», «truepart», «falsepart»)

So if there is no . then it will just return the full string, something like this:

IIf(InStr(1,[DNS],".")>0, Left([DNS],InStr(1,[DNS],".")-1), [DNS])

 

[SourChipmunk]

That works perfectly! Thank you very much!

Furthermore, I understand why it works.