How to resolve 30/12/1899 as Date??

Hi forum, I have included in my code to append records to a table as the code Loops.

In the Immediate Window, the records show correctly with correct dates.
But the Appended Records all show as 30/12/1899

I am pretty sure this date is incorrect as I wasn't even born then although sometimes my children would have thought so

Here is the Insert into sql which results in 30/121899

	Code:
	                sqlString = "INSERT INTO TblLateFeeCalculated ( LDPK, LateFeeAmount, LateFeeDate ) " & vbCrLf & _
                    "VALUES (" & LoanID & ", " & LateFeeNow & ", " & CommenceDate & ");"
                DoCmd.RunSQL sqlString

yet this Debug.Print results in the correct Non US date

	Code:
	Debug.Print LoanID & " " & LateFeeNow & "  " & CommenceDate

Appreciate any advice.


Post your answer or comment

comments powered by Disqus
Hi folks,

I am a VBA newbie, and I have put together a CRM utility with a few date fields. These date fields are filled using a popup calender. Whn I now erse a date previously entered, and fill the field afterwords with a new date, Access shows 30.12.1899. Only when I enter the correct date a second time, is is displayed correctly. Here is my "buggy" code, the date field's name is Angebot:

Private Sub Angebot_Click()
On Error GoTo ErrorHandler
Exit Sub
ErrorHandler:
Angebot.Value = Nothing
Set cboOriginator = Me!Kalender
cboOriginator.Value = Kalender.Value
cboOriginator.SetFocus
Kalender.Visible = False
Set cboOriginator = Nothing
End Sub

Private Sub Angebot_MouseDown(Button As Integer, Shift As Integer, X As
Single, Y As Single)
Set cboOriginator = Angebot
Kalender.Visible = True
Kalender.SetFocus
Kalender.Value = Angebot.Value
End Sub

I had to build in the error handler, becaue without it, I was getting a "run time error 91", on top of the above bug. Any idea on how to fix it?

Hi folks,

I have the following sql statement in my code.


	Code:
	    strSQL = "INSERT INTO tbl_iss_num (doc_tbl_fk, Issue_num, Date_Released) VALUES " & _
             "(" & fk_ver & ", '" & Me!txt_ver_no & "'," & Date & ")"

When I use db.execute (strSQL) the value inserted into the table by the date function is 30/12/1899. Anyone know why this is happening?

I have these pieces of codes inside my form (name: frmBOMComparisonQuantity)
Quote: If optStandardQty.Value Then
DoCmd.OpenQuery "Report_BOM_Comparison_StandardQty_WithoutUnitPric e", acViewNormal
ElseIf optBalanceQty.Value Then
DoCmd.OpenQuery "Report_BOM_Comparison_BalanceQty_WithoutUnitPrice ", acViewNormal
End If I have inserted below code into the QUERY : Report_BOM_Comparison_StandardQty_WithoutUnitPrice and Report_BOM_Comparison_BalanceQty_WithoutUnitPrice . Both queries are the pivot table format.

Quote: [Forms]![frmBOMComparisonQuantity]![txtOperator] However, i obtain error message as attached during execution. Anyone has idea how to resolve the problem?

Hi Forum,

I have created a form with code etc that will produce letters and update tables accordingly.

One pce of data I can not get correct is the User ID.

When we log on we use our password against a login script - see jpg below.

TBLTeamMembers has fields TeamID (Initials per logon script), firstname, lastname etc.

I want to get the Intials (TeamID) to be used in one of the Append tasks being performed.

What ever I try, it always sends an error message where I can type in the initials and all is fine.
But I don't want to type my initials 700 times

Here is the sql that does the appending

	Code:
	 sqlString = "INSERT INTO tblLoanComm ( LoanID, CommNotes, Operator, CommDate, CommTime ) " & _
        "SELECT TBLLOAN.LDPK, ""Sent "" & [TblLetterOptions].[LetterType] & "" Letter"" AS CommNotes, TBLTeamMembers.TeamID,
Date() AS CommDate, Time() AS CommTime " & _
        "FROM TBLLOAN, TblLetterOptions, TBLTeamMembers " & _
        "WHERE (((TBLLOAN.LDPK)=" & LDPK & ") AND ((TBLTeamMembers.TeamID)=" & strUserID & ") AND
((TblLetterOptions.LetterID)=" & LetterID & "));"

The error message appears and I type my Initials and all works fine with my initilas UpperCase as per the Dim here

	Code:
	 Dim sqlString, strUserID As String
    Dim ADPK, LDPK, LetterID As Integer
    
    ADPK = Me.ADPK
    LDPK = Me.LDPK
    LetterID = Me.LetterID
    strUserID = UCase(CurrentUser)

This means the system picks up that strUserID should be Uppercase but it doesn't pickup that it should be CurrentUser.

Any suggestions of how to resolve this??
Thanks

Hi,

I want to update one table field from other table through the passing of parameter. If I pass the parameter 30/11/2011, the numeric field of the table [Nov_A] should be updated as per the other table's numeric field [Nov-11]. same like for December 2011, January 2012 and so on. I have written some code is showing the following error.

Run-time error '3129'

The code are as follows:
Private Sub cmdTest_Click()
Dim db As Database, rs As Recordset
Dim mysql As String
Dim Vdate As Date
Set db = CurrentDb()
Set rs = db.OpenRecordset("select * from ActualsTransferDate")
Vdate = InputBox("Enter the Date:")
While Not rs.EOF
rs.Edit
rs!transferdate = Vdate
rs.Update
rs.MoveNext
Wend
rs.Close
db.Close
If Vdate = Format(#11/30/2011#, "mmm") Then
mysql = "UPDATE ((prj1 INNER JOIN [bbk24 - revised] ON prj1.[Project #] = [bbk24 - revised].[Project #]) INNER JOIN tblCostData ON prj1.OracleProjNo = tblCostData.Project) INNER JOIN [BdgBkNo for View] ON [bbk24 - revised].BkNo = [BdgBkNo for View].BkNo SET [bbk24 - revised].Nov_A = [Nov-11];"
End If
DoCmd.RunSQL mysql
End Sub

If anybody help me to solve this problem I will grateful.

Haider

Help!

I have created a crosstab query for which I would like to have dynamic values for the column heading in the "yyyy-mm" format. I need to display all the months within a defined period across the pivot regardless if there is any data for that month. I have ensured that this happens by linking the [Month] field with an outer join to a date table and setting the criteria to filter for the date range. At runtime, the Column Heading property is set for the months queried. If I re-query using a different date range, the initially defined column heading property remains the same.

I would like to pre-define the values of the columns using start date and end date text boxes on a criteria form. If I could run a query that would define the recordset, I should be able to insert those values into the IN clause of the PIVOT statement. I have run into problems on that end because the following argument does not work:

Dim ColumnHeading As DAO.Recordset
Set ColumnHeading = db.OpenRecordset("qryMyRs", dbOpenSnapshot)

strSQL = "TRANSFORM...
strSQL = strSQL & "PIVOT tblDateRange.[Date Label] In ColumnHeading "
QD.SQL = strSQL

Would appreciate any suggestions on how to resolve this problem.

Hi,

I've got a new database that i am trying to get to work...

It's mainly centred around budgets and actuals...

I've got two tables tblBudget and tblActual

With twelve fields in each... Budget Month 1, Budget Month 2 etc and Actual Month 1, Actual Month 2 etc

I've managed to link these together and begin to play some tunes on variance etc and calculate cummulative numbers etc

BUT

I'm being asked to show "current month" budget vs actual and "year to date" budget vs actual and then calculate the end of year forecast based on current actuals and remaining budget...

My major problem is how to get the current month selecetd...

I was figuring that if I created a form with combo boxes where I could pick Budget Month 2 and Actual Month 2 I could somehow within my query look those up as the current month...

Does that make sense?

How can I pick one field out using a combo box selection?

i.e. if i pick Budget Month 2 can i get the query to only return the number for that month from the entire record?

I have a field (from a table I did not define) in my query called P_INVOICE_DTE and it is defined as a long integer.
I added my own new field to the QBEGrid called Date: with the following code:
Date: DateAdd("d",[P_Invoice_Dte],"12/31/1971")

This converts the long integer back into a relevant (accurate) date for me-all is good.

Now I want to setup a parameter query on this new field called Date.
Between [Enter Beginning Date] And [Enter Ending Date]
....but this does not return the correct recordset (it returns all records)

when I put in a literal date like #11/16/2004# it returns correctly.

The SQL Expression looks like this:
SELECT HINVDB_S_ORD_HDR.P_SLSID, HINVDB_S_ORD_HDR.P_INVOICE_DTE, DateAdd("d",[P_Invoice_Dte],"12/31/1971") AS [Date], HINVDB_S_ORD_HDR.P_INVOICE, HINVDB_S_ORD_DTL.P_ORDNO, HINVDB_S_ORD_DTL.P_INVOICE, HINVDB_S_ORD_DTL.P_ITEM, HINVDB_S_ORD_DTL.P_DESCRIPTION__1, HINVDB_S_ORD_DTL.P_DESCRIPTION__2
FROM HINVDB_S_ORD_DTL INNER JOIN HINVDB_S_ORD_HDR ON HINVDB_S_ORD_DTL.ROWID = HINVDB_S_ORD_HDR.ROWID
WHERE (((HINVDB_S_ORD_HDR.P_SLSID)=[enter rep#]) AND ((DateAdd("d",[P_Invoice_Dte],"12/31/1971")) Between [Enter Beginning Date] And [Enter Ending Date]));

I believe I have to somehow indicate to Access 2000 SP3 that this new field I created is a Date by using the # symbols, but I do not
know the proper syntax to reference this.
Can anyone offer some guidance?

Suzanne

Hi, everyone, this is my first time be here, I'm glad that I found a good place to learn ACCESS.

I found this DB sample in here
http://www.access-programmers.co.uk/...d.php?t=103312
but if I try to add two text box in the mainform to query startdate and enddate, how do I make it.

such as: I want to find title is manager and sex is F and datebirth from 1965/1/1~1973/12/31.

I'm lack skill in Access and tried to find this answer for a long time, every suggestion will be appreciated, thanks so much in advance.

Hanna

please find attached table
when saving a record first one is blank row how to solve. for the firsttime only it happeneds why

how to solve . Please help me as early as possible Attached Thumbnails   Reply With Quote 12-10-2012, 03:58 AM #2 RuralGuy Administrator Windows 7 64bit Access 2010 32bit Join Date Mar 2007 Location 8300' in the Colorado Rocky Mountains Posts 9,484 How are you saving the record?

I have seen this issue posted a huge amount of times so I am going to post how to resolve this issue.

Access does not have any type of wait, pause or sleep functions. So the question is always, "How do I make Access VBA wait for some length of time before going on?"

The typicall answer is to create a loop such as:


	Code:
	For i = 1 to 10000000
Next

While this does take time to process it is unreliable and may result in an error. VBA does have the proper tools to be able to create a wait.


	Code:
	TWait = Time
TWait = DateAdd("s", 15, TWait)
Do Until TNow >= TWait
     TNow = Time
Loop

First we have to establish the time that it is now - TWait = Time.

Next we have to determine how long we want to wait. I am using the DateAdd function to add 15 seconds to TWait. For more on the DateAdd funtion use help in Access.

Now, we need to prevent the VBA coding from moving to the next block of code and for that we are using the loop. The Do Until loop does just that, it loops until TWait is greater than or equal too TNow. Each time through the loop the current time is evaluated and then compared to our wait time and if the time to now is not greater than the wait time the code will loop again.

I had to add the greater than to the loop because I found that the time only displays in HH:MM:SS this is not the case for the variable so if the time passes then it is ok to move on.

I had to use this to make Access VBA wait while I was trying to send keystrokes to another program. There is a download lag that I needed to wait until it was complete before I could send the next set of keys. I have not had an issue with this option for waiting.

Hope you find this helpful.

Hi everyone

i would like to know if it is possible to pull the max and min start date and end date.

basically i have an option where by the user selects the desired date range that they would like.

now i would like to know how to get the earliest and latest date range should they not select a date range.

N.B my date range is being displayed at the top of my report.

i have researched this and still nothing has come up.

please help

Hi:
MS Access
In Table:

I want to make the input mask as mmddyy

Date/Time datatype
when the user enter the date value eg. 072105 => 7/21/2005 (system change)
I want to skip the slash character.

Can I do that in table?

I know that Text datatype, there is one input mask __/__/__
But, I want to use Date/Time datatype.
Please let me know.

Thanks.

Hi all,

My question is how to reverse to TRANSFORM/PIVOT operation on a Access table?

I have a table like this (you just see the first rows here):


Date USD GBP CHF JPY CAD DEM
1/01/1979 0,694622 1,572711 0,435450 0,002920 0,596779 0,402777
1/02/1979 0,694622 1,572711 0,435450 0,002920 0,596779 0,402777
1/03/1979 0,694622 1,572711 0,435450 0,002920 0,596779 0,402777
1/04/1979 0,694622 1,572711 0,435450 0,002920 0,596779 0,402777
1/05/1979 0,694622 1,572711 0,435450 0,002920 0,596779 0,402777
1/06/1979 0,694622 1,572711 0,435450 0,002920 0,596779 0,402777
1/07/1979 0,694622 1,572711 0,435450 0,002920 0,596779 0,402777
1/08/1979 0,694622 1,572711 0,435450 0,002920 0,596779 0,402777
...

The idea is to pivot it like this :


Date Currency Rate
1/01/1979 USD 0,694622
1/01/1979 GBP 1,572711
1/01/1979 CHF 0,435450
1/01/1979 JPY 0,002920
1/01/1979 CAD 0,596779
1/01/1979 DEM 0,402777

1/08/1979 DEM 0,402777

I think this is not possible in ACCESS SQL with the TRANSFORM/PIVOT command, because it works the other way : from tabular to columnar, not reverse.

Anyone ideas how to resolve this thing?

Thanks you very much!


With kind regards,

Stijn Raymaekers
Belgium

Access 2010 - In datasheet view, some of my yes/no fields display according to the format set in design view (yes/no, true/false, etc., or -1/0 if no format specified). But a few fields that I have added since originally creating the table show up as check boxes.

I cannot find any discussion of how to force the checkbox display or even figure out where that is specified. This same behavior carries over to DS forms as well.

How can I specify a checkbox display style instead of Yes/No etc?

I'm trying to put together some statistical reports using a crosstab query, and failing miserably.

Here is what I would like to do:

I have 5 fields (A, B, C, D, E) for ranking items. The available values are 0,1,2,3. What I want to do is show how many of each number was entered in a given time period. The user will enter the date range (have this set up and working).

It should look something like this:

0 1 2 3
A 4 6 10 8

B 5 7 0 6

C 2 10 11 8


I did a bunch with crosstab queries several years ago and had great success. I tried for hours to get it to work in this case, but I can't figure out how to set the column headings as the number values, and to deal with the multiple fields for row headings. I don't care if they are reversed (i.e. A, B, C on the top.

Any thoughts would be deeply appreciated.

My system prompt the error :
DATA TYPE MISMATCH IN CRITERIA EXPRESSION

How to know which fields contribute to the problem above? My SQL statement is as following

insert into Master_Drawing_Comparison (ModuleNo,DrawingNo,Description,Operator,StandardO rFabrication,StandardQty,BalanceQty,UOM,RackNo,Cur rency1,SellingPrice,BuyingPrice,AdminTransportCost ,AssemblyCost,Supplier,ManufacturingPartNo,LastUpd ateDate)select ModuleNo,DrawingNo,Description,'00477',Type,SumOfS tandardQty,BalanceQty,UOM,RackNo,Currency1,Selling Price,BuyingPrice,AdminTransportCost,AssemblyCost, Supplier,ManufacturingPartNo,LastUpdateDate from V_Master_Drawing_Comparison2 where ModuleNo='2380910'

Any expert here can advise me something ?

There are several links out there that shows how to figure out the references to controls. However, instead of typing and guesisng, Microsoft has provided a easy way to grab the reference using Expression Builder. This can be used within Form Design View or Query Builder.

To open Expression Builder in Form Design View:

Ciick the ellipse button on right side of the properties windows for the appropriate property (in most cases you may use Control Source, Default Value and maybe on rare occasions, Events)


To open the same builder from Query Builder, right-click on the Criteria row for the column where you want to reference the control:


This will then bring up the Expression Builder:


If you opened this from the Form Design, it auto select the form in question as noted above. Likewise, the query will be also there in first row if opened from the Query Editor.

If you want to reference another form (or a subform), you need to navigate through the Loaded Forms:


Note: Don't bother with "All Forms" as you may get an error in event if they are not opened. If it's not in the Loaded Forms list, then you need to rethink the design and verify that the form will be open at the time the expression is called.

You then can select controls from this list:


Alternatively, you can select directly from form's recordset, which IMHO, is a bit safer than getting it through a control:


Note: If you select , you will then need to select one of field on the rightmost pane, unlike selecting the control which is in the middle pane.

You now have the references. You also can add functions, both built-in and your custom functions from the Function folders on the leftmost pane.

HTH.

Hi,

I get a "run-time error "3464" everytime I Add, Update and Delete a record based on GroupID. In my table the data type for GroupID is number and the other two fields are text. I would like to know how to declare my GroupID filed as an interger or number? BTW, i have no idea about VBA :-) ..please help.


Code: Option Compare Database Option Explicit Dim sql As String Private Sub cmdAdd_Click() sql = "INSERT INTO tblGroup(GroupID,Section,Unit) " & "VALUES ('" & GroupID & "','" & txtSection & "','" & txtUnit & "')" DoCmd.RunSQL (sql) lst1.Requery End Sub Private Sub cmdDelete_Click() sql = "DELETE FROM tblGroup WHERE GroupID = '" & GroupID & "'" DoCmd.RunSQL (sql) lst1.Requery End Sub Private Sub cmdUpdate_Click() sql = "UPDATE tblGroup SET GroupID = '" & GroupID & "', Section = '" & txtSection & "', Unit = '" & txtUnit & "' WHERE GroupID = '" & lst1.Column(0) & "'" DoCmd.RunSQL (sql) lst1.Requery End Sub Private Sub lst1_Click() GroupID = lst1.Column(0) txtSection = lst1.Column(1) txtUnit = lst1.Column(2) End Sub

Hi All,

I'm new to MS Access forms. I have a combobox with 3 fields A, B, C. and a textbox. When the option B is selected the length of the characters in textbox should not exceed 3. I'm trying to write VBA code for this as below

Private Sub TEXTBOX_KeyPress(KeyAscii As Integer)
If [TEXTBOX].length && [COMBOBOX] = "B" Then
MsgBox "Error"
End Sub

But the "length" field is not available in TEXTBOX, Could any one of you please let me know on how to resolve this.

Thanks you.

I needs some advice on how to proceed.
Our company is devolving some operations to a sub office for 3 out of 11 business units. we have a central DB that we use to record monthly events the problem is that the parent group has to be able to update records for all units but the sub office is only allowed to see and edit their records

At the moment the data is all stored in one table and we use a Front end back end with local front ends for access I have a table to say if a unit is part of the sub office or not and in the sub office front end i have a Query which links the 2 tables together and filters the records to show only sub office

SELECT tbldatatable.*, tblShipName.Manager
FROM tblShipName RIGHT JOIN tbldatatable ON tblShipName.ShipName = tbldatatable.Ship
WHERE (((tblShipName.Manager)="nv"));
the remainder of the forms and querys all run from this query rather than the tables

the problem is the join makes it a non updatable record set

what is the advice on how to achieve what i need as i cant see a simple way to proceed
thanks
ND

Hi folks - I saw a thread last week re how to extract the year from the date field possibly using date serial but can't find it can anyone help - it is probably very simple but i can't get it to work.

Ta

Humph

Hello, I am not entirely sure how to go about normalizing this table. It contains machine settings for manufacturing lines. Pretty much each line is broken down like this:

Each Line has quite a lot of settings ranging from 200-600 machine settings (these need to change so I put them as records instead of fields), these aren't the same line from line as some lines are made of different components. There's about 10 lines (though more may be added).

Each line runs between 10-60 different products (these change also) and each product runs at their own machine settings.

I am not entirelly sure how to go about doing this as if there are say 1 run with 200 settings (which is pretty much the lowest) and runs 10 products, thats already 2000 settings right there.

Any ideas?
Here's a picture of my relationships to see how the tables are set up
Here's my database, no information in it yet though

Am I on the right track of setting my database up? I am not entirely sure, I am still learning access and I have been reading up a lot on normalization but I never seen a example of a database that is doing what I am trying to achieve. How would you go about normalizing this project?

Thank you very much in advanced!!!

When a query is run, i want a prompt displayed allowing the user to enter
< DATE to find all records less than the specific date. e.g. < 1/1/2011.

I know how to do that with a start date AND end date (Between [Enter Beginning Date] And [Enter End Date]), but that's not what I'm looking for.

The records will be updated by small number of users with a Last Reviewed date every 2 months. i'll just want to check for records that have not been updated in the last 3 weeks or so of running the query.


Not finding an answer? Try a Google search.