Jumat, 30 September 2016

Re: [MS_AccessPros] Simple Default value question

 

Jim-

Sometimes referencing an earlier calculated column alias in a query works, and sometimes it doesn't.  The SELECT part should be:

SELECT Date() As CurrentDate, Year(Date()) As CurrentYear

John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications

On Oct 1, 2016, at 01:02, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I just discovered something odd about this issue. I am taking this database over for someone that retired and there is a macro that has a delete query and then an append query that appends to a table. the SQL is below. Apparently the current year needs to be a number somewhere down the line for some reason or another. I have not gone that deep in the database yet.

In the macro

Delete Query
the comment in the macro states the following
    deletes the LU table (LU_CurrentDateYear) of current date and current year field (this is so we don't have the date problem of coming in as text (Cdate thing)

DELETE LU_CurrentDateYear.*
FROM LU_CurrentDateYear;

Append query
the comment in the macro is
 populates the LU table (LU_CurrentDateYear) with today's date and the year part of that

INSERT INTO LU_CurrentDateYear ( CurrentDate, CurrentYear )
SELECT Date() AS CurrentDate, DatePart("yyyy",[CurrentDate]) AS CurrentYear;

 LU_CurrentDateYear table

the CurrentYear field has the following properties
Data Type: Number
Field Size: Long Integer




 
Jim Wagner


On Friday, September 30, 2016 3:32 PM, "luvmymelody@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


 
Hi all,

I am trying to make a field in a table to have a default value of the current year. I have tried a lot of variations like =Year(Now()) but I am getting 1905.

Why is this happening?

Thank You

Jim Wagner


__._,_.___
Posted by: John Viescas <johnv@msn.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (3)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

.

__,_._,___
Diposting oleh di Tidak ada komentar:

Re: [MS_AccessPros] Simple Default value question

 

I just discovered something odd about this issue. I am taking this database over for someone that retired and there is a macro that has a delete query and then an append query that appends to a table. the SQL is below. Apparently the current year needs to be a number somewhere down the line for some reason or another. I have not gone that deep in the database yet.

In the macro

Delete Query
the comment in the macro states the following
    deletes the LU table (LU_CurrentDateYear) of current date and current year field (this is so we don't have the date problem of coming in as text (Cdate thing)

DELETE LU_CurrentDateYear.*
FROM LU_CurrentDateYear;

Append query
the comment in the macro is
 populates the LU table (LU_CurrentDateYear) with today's date and the year part of that

INSERT INTO LU_CurrentDateYear ( CurrentDate, CurrentYear )
SELECT Date() AS CurrentDate, DatePart("yyyy",[CurrentDate]) AS CurrentYear;

 LU_CurrentDateYear table

the CurrentYear field has the following properties
Data Type: Number
Field Size: Long Integer




 
Jim Wagner


On Friday, September 30, 2016 3:32 PM, "luvmymelody@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


 
Hi all,

I am trying to make a field in a table to have a default value of the current year. I have tried a lot of variations like =Year(Now()) but I am getting 1905.

Why is this happening?

Thank You

Jim Wagner


__._,_.___
Posted by: Jim Wagner <luvmymelody@yahoo.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (2)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

.

__,_._,___
Diposting oleh di Tidak ada komentar:

[MS_AccessPros] Simple Default value question

 

Hi all,


I am trying to make a field in a table to have a default value of the current year. I have tried a lot of variations like =Year(Now()) but I am getting 1905.


Why is this happening?


Thank You


Jim Wagner

__._,_.___
Posted by: luvmymelody@yahoo.com
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (1)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

.

__,_._,___
Diposting oleh di Tidak ada komentar:

Re: [MS_AccessPros] Re: Convert Ribbon from Access 2007 to Access 2016

 

Ok I have generate xml file using accessribbon.de (versione 2016) but if I put it in USysRibbons table is ok, if load ribbons  with:

Application.LoadCustomUI "MyRibbon , xml_File

is not ok.

thanks a lot

Domenico




2016-09-29 21:23 GMT+02:00 yahoo@craven.de [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>:
 

OK, so it's a multilingual reason,


I had similar situations and solved it in two different ways.

The first way is to use the callbacks to set the texts/labels in the ribbon dependaent on the language chosen. The language can in my case from a translation table. but you could use a table or further INI files.

The second was to import the ribbon xml into the USysRibbons Table and have the ribbon available by the next restart of the application. If the ribbon was called MyFormRibbon then there would be a MyFormRibbon_1031 for German, ..._1033 for US English, ..._1036 for French, etc.

Your third solution would be to generate new versions of the XML directly compatible with newer Access Versions. For this you could use one of the free or trial tools for Access Ribbons, analyse the differences in the XML generated, and edit your XML accordingly.

Yours,
Andrew


__._,_.___
Posted by: Domenico Cozzolino <domcoz@gmail.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (7)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

.

__,_._,___
Diposting oleh di Tidak ada komentar:

Kamis, 29 September 2016

[MS_AccessPros] Re: Convert Ribbon from Access 2007 to Access 2016

 

OK, so it's a multilingual reason,


I had similar situations and solved it in two different ways.

The first way is to use the callbacks to set the texts/labels in the ribbon dependaent on the language chosen. The language can in my case from a translation table. but you could use a table or further INI files.

The second was to import the ribbon xml into the USysRibbons Table and have the ribbon available by the next restart of the application. If the ribbon was called MyFormRibbon then there would be a MyFormRibbon_1031 for German, ..._1033 for US English, ..._1036 for French, etc.

Your third solution would be to generate new versions of the XML directly compatible with newer Access Versions. For this you could use one of the free or trial tools for Access Ribbons, analyse the differences in the XML generated, and edit your XML accordingly.

Yours,
Andrew

__._,_.___
Posted by: yahoo@craven.de
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (6)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

.

__,_._,___
Diposting oleh di Tidak ada komentar:

Rabu, 28 September 2016

Re: [MS_AccessPros] #Error in a report on a calculated field

 

Try remove the = or just select the field fro the control source drop down. 

Get Outlook for iOS




On Thu, Sep 29, 2016 at 2:28 AM +0200, "jovaughn@rochester.rr.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:



Actually I started with just the field name and received the same #Error in the reports field but in the Query field the days of age appear.
I'm a newbie to this so I am probably doing something stupid.
Joanne


__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (4)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

.

__,_._,___
Diposting oleh di Tidak ada komentar:

RE: [MS_AccessPros] |1 in query expression '|2'

 


So I did as suggested, removed Access and reinstalled runtime version 2007. I am still having this problem.

Any other suggestions?. Anybody?

Thanks
Sarah

---In MS_Access_Professionals@yahoogroups.com, <andrew@...> wrote :

Hi Sarah,

 

The fact that the end-users are connecting to the server via RDP and running their own Remote Desktop session means that the OS and the version of Access running on their local PC's is irrelevant.  The problem is with the server and the version of access that's running on it.  FWIW, I've had some really peculiar problems with the 2013 & 2016 Access runtimes running on both Server 2012 & Windows 7/8.1/10, particularly Windows 10.  In each case, I solved the problems by completely removing & reinstalling Access on the affected machines.

 

A few things to check:

-          Make sure that Access was installed correctly on the RDP server.  The server needs to be switched into Install mode before installing any application software.  If it wasn't, completely remove Access, reboot the server & install it properly.

-          Make sure there aren't any older versions of Access on the same server as IME this can cause some weird problems.

-          Try creating a new database & importing all the objects from the original database.  This sometimes fixes unexplained problems.

-          Is zForm21 a Report Selection form?  If so, what I tend to do in situations like this is to create the query SQL in VBA and plug the actual field values into the query rather than referencing the form controls.  This usually results in a smaller wury that is much easier to debug if things aren't working as expected.

 

I hope this is of some help.

 

Regards,

Andrew

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 27 September 2016 18:53
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] |1 in query expression '|2'

 

 

 

It is a huge undertaking to convert the db to 2007 and I am hoping for a solution.

 

Yes ZFORM21 is open and I understand about join, but the query does what it is supposed todo. 

 

So how come this works on some computers (also running Access 2013) and not on others?.


Sarah


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Sarah-

 

I assume zform21 is open and the referenced controls have values.  You should realize that your LEFT JOINS are meaningless because you have a filter (shift.[Shift Name]) on one of the columns in a "right" table.  You'll end up with effectively an INNER JOIN between student and shift.

 

That error message implies that the query is not "compiled", and Access is getting an undefined error when it tries to compile it.  It's not a good idea to try to run and old .mdb-based application in 2007 and later.  If you have the original mdb, can you convert it to accdb under 2007, then create the run-time file?

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Access Applications

 

On Sep 27, 2016, at 16:40, sarahk@... [MS_Access7 _Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

sql:

 

SELECT student.[student id], student.[last name], student.[first name], student.mi, student.[end date], student.dob, age([dob],Date()) AS age, student.alzpro, student.sex, student.advd, student.drcd, student.aid, student.sneed, student.[billing type], shift.[Shift Name], student.[start date], physician.*, student.address, student.address1, student.city, student.state, student.zip, student.[tel #1], student.[cl#], student.are, student.[eclast name], student.[ecfirst name], student.ecrelation, student.ecaddress, student.ecaddress1, student.eccity, student.ecstate, student.eczip, student.[ectel#], living.living, religion.religion, student.sun, student.mon, student.tue, student.wed, student.thu, student.fri, student.sat, student.[billing type]

FROM (((student LEFT JOIN shift ON student.[preferred shift] = shift.[Shift #]) LEFT JOIN physician ON student.drcd = physician.drcd) LEFT JOIN religion ON student.religioncd = religion.Religcd) LEFT JOIN living ON student.livingcd = living.livcd

WHERE (((student.[end date]) Is Null) AND ((student.[billing type]) Like [Forms]![zform21]![pmb]) AND ((shift.[Shift Name]) Between [Forms]![zform21]![sfrom] And [Forms]![zform21]![sto]) AND ((student.[start date])<=[forms]![zform21]![pid to]) AND (([last name] & ", " & [first name] & " " & [mi]) Between [Forms]![zform21]![ln from] And [Forms]![zform21]![ln to]) AND ((nz([student]![insrcd],"")) Between nz([Forms]![zform21]![insfrom],"") And nz([Forms]![zform21]![insto],""))) OR (((student.[end date])>[forms]![zform21]![pid to]))

ORDER BY student.[last name], student.[first name], student.mi;



---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Sarah-

 

Do you know which query is breaking?  And if so, what's the SQL?

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Access Applications

 

On Sep 27, 2016, at 00:11, sarahk@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

"|1 in query expression '|2'"

 

I get this message when I run a query on one of the computers on a network. None of the other computers on the network are having this problem. The other computers have MS Access 2013 installed, the database is located on a Windows 2012 Server every computer has their own front end (mde) (located on the server) and are sharing the back end which is also located on the server.

The computer that is having the problem, is running windows 7 (as are the other computers) and is connecting with RDP to the windows 2012 server and running the application from the server. The server has MS Access 2013 runtime version installed, I also tried 2007 and 2010 runtime, it did not make a difference, I still get the same error. I also tried running the application directly on the sever (not with RDP)  and I still get the same error. All the other forms/queries / reports etc are working.

The application was created in MS Access 2003.

 

Does anybody have any ideas why the server is getting this error? I tried googling but none of the answers made sense.

 

As always all help is appreciated.

 

Sarah

 

 

 

 

 

 

__._,_.___
Posted by: sarahk@schemesoftware.com
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (7)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

.

__,_._,___
Diposting oleh di Tidak ada komentar:

Re: [MS_AccessPros] #Error in a report on a calculated field

 

Actually I started with just the field name and received the same #Error in the reports field but in the Query field the days of age appear.

I'm a newbie to this so I am probably doing something stupid.
Joanne

__._,_.___
Posted by: jovaughn@rochester.rr.com
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (3)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

.

__,_._,___
Diposting oleh di Tidak ada komentar:

[MS_AccessPros] Re: Convert Ribbon from Access 2007 to Access 2016

 

Well Domenico, the RibbonXML will load from the USysRibbon Table ,unfortunately I can't upload a Screenshot here but, apart frm error messages because of the missing Callback Functions, It does appear.


I suspect that although Access will handle the old RibbonXML when loading from the USysRibbons Table, it possibly does not do so when loading as you are, "live".

As the RibbonXML looks static, why are you loading it from a text file?

Yours,
Andrew

__._,_.___
Posted by: yahoo@craven.de
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (5)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

.

__,_._,___
Diposting oleh di Tidak ada komentar:

Re: [MS_AccessPros] Create row user log

 

Thank You Duane, I will give that a shot. I will have to put it on hold for now. Bigger project to take care of.


Jim Wagner


On Wednesday, September 28, 2016 10:22 AM, "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


 
Jim,

Me.txtUser.DefaultValue = """" & Environ("UserName") & """" 

I don't have a clue regarding the code to add new record.

Duane Hookom


From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, September 28, 2016 11:39 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Create row user log
 


Duane

So I tried that. I clicked the add new record button and no new record is added and the text box control shows #Name?


 
Jim Wagner


On Wednesday, September 28, 2016 9:26 AM, "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


 
Jim,

Consider setting the default value of the text box to the user rather than the value. 

Duane Hookom, MVP
MS Access


From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, September 28, 2016 11:14 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Create row user log
 


Hello all,

We have a database that has 1 backend and 6 frontends. Someone keeps adding a blank record and no one is admitting it. I believe that it is either a bug or probably user error. I opt to think that it is user error.

I would like to create a user log for any new record being added to the table. I have part of the solution but a piece of the parts is doing something I am unsure of.

On the form load event for the form I have the following code to populate a text box named txtUser with the user log. this works wonderful. The issue is when I click the add new button on the form the record is added and the date and the log in are populated, but the txtUser control goes blank. I am then unable to use the add new record button to get the data to put in the table. I am unable to explain why the text goes away.
I added the same code for the environ to the form load event also and the text disappears in the control.

Is there something I am missing or I am on the wrong track going to the wrong town?

Thank You

Jim Wagner

Form Load Event

Private Sub Form_Load()

Me.txtUser = Environ("UserName")

End Sub

__________________________________

On the before update event of the form I have the following code

Private Sub Form_BeforeUpdate(Cancel As Integer)

CurrentDb.Execute "INSERT INTO [Essential Functions] (DateofEdit, User) " & _
   "Values(Date() + Time(),  '" & Me.txtUser.Value & "')", dbFailOnError
  
   Me.txtUser = Environ("UserName")

  
End Sub








__._,_.___
Posted by: Jim Wagner <luvmymelody@yahoo.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (5)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

.

__,_._,___
Diposting oleh di Tidak ada komentar:
Langganan: Postingan (Atom)